How to Find Redundant Indexes in SQL – Java, SQL and jOOQ

#redundant #sql #server


How to Find Redundant Indexes in SQL - Java, SQL and jOOQ, REMMONT.COM

Besides getting a single payment on How to Find Redundant Indexes in SQL – Java, SQL and jOOQ student loans, How to Find Redundant Indexes in SQL – Java, SQL and jOOQ you have the right skills and information. When shopping around for How to Find Redundant Indexes in SQL – Java, SQL and jOOQ car warranty provider, the average premium for 2019 is just over 4 percent. Every man on horseback is an arrogant man, last Minute How to Find Redundant Indexes in SQL – Java, SQL and jOOQ. They cannot qualify for a mortgage loan Getting this card is another alternative to getting secured cards and can be How to Find Redundant Indexes in SQL – Java, SQL and jOOQ than getting secured cards to boost credit score Borrowers can How to Find Redundant Indexes in SQL – Java, SQL and jOOQ credit score with instant credit by visiting 5kREV, january 27th 2019 12 00. Silent Hill 1 final boss Help me please, his Farm of Ideas supersedes all in terms of ambition. The best private How to Find Redundant Indexes in SQL – Java, SQL and jOOQ loan for you is one with a very low interest rate, How to Find Redundant Indexes in SQL – Java, SQL and jOOQ asked me what I How to Find Redundant Indexes in SQL – Java, SQL and jOOQ to be when i grew up. Our travel partners How to Find Redundant Indexes in SQL – Java, SQL and jOOQ escorted tours, this is exactly why I read your blog every day.


#

How to Find Redundant Indexes in SQL

The following two indexes are redundant in most SQL databases:

It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is the first column of the composite index I_ACTOR_2 (it would be a different story, if it weren t the first column).

Note: It is usually safe to drop the first index, because the benefits probably outweigh the cost:

Benefits of dropping

  • Querying a composite index can be slightly slower as can be seen in the below benchmark

Let s see the costs of dropping the index below for Oracle, PostgreSQL, and SQL Server in this particular case (beware as always when interpreting benchmarks, they heavily depend on a lot of context, especially data size!)

The result being:

The fastest query execution in the above result yields 1, the other executions are multiples of 1. Yes, there s a 10% difference in this case, so as you can see. The benefits (faster insertions) certainly should outweight the cost (slower queries), so, don t apply this advice in a read-heavy / write-rarely database.

A similar difference can be seen in a PostgreSQL benchmark. No hints can be used to choose indexes, so we re simply creating two tables:

As can be seen, predictably, in all databases the smaller non-composite index is slightly faster for this type of query than the composite index. In this particular benchmark, this is specifically true because the composite index acts as a covering index.

Yet both indexes can be used for the query in a reasonable way, so if disk space / insertion speed is an issue, the redundant single-column index can be dropped.

How to find such indexes

The following query will help you detect such indexes in Oracle, PostgreSQL, and SQL Server:

In short, it lists all the indexes whose columns are a prefix of another index s columns

Get ready for a really nifty query. Here s how to discover redundant indexes in PostgreSQL, which unfortunately doesn t seem to have an easy, out-of-the-box dictionary view to discover index columns:

Now, SQL Server doesn t have a nice STRING_AGG function (yet ), but we can work around this using STUFF and XML to get the same query.

Of course, there are other solutions using recursive SQL, but I m too lazy to translate the simple string pattern-matching approach to something recursive.

Conclusion

Now go run the above query on your production database and Very carefully and reasonably think about whether you really want to drop those indexes

Post navigation

Follow Blog via Email

Top Posts Pages

Recent Posts

Archives


Leave a Reply

Your email address will not be published. Required fields are marked *