Over-Indexing


If the concept of function-based indexing is new to you, you might be tempted to just index everything, but this is in fact the very last thing you should do. The reason is that every index causes ongoing maintenance. Function-based indexes are particularly troublesome because they make it very easy to create redundant indexes.

About our book “SQL Performance Explained”
This book is definitively worth having in the company library.
” — Joe Celko

The case-insensitive search from above could be implemented with the LOWER function as well:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE LOWER(last_name) = LOWER('winand');

A single index cannot support both methods of ignoring the case. We could, of course, create a second index on LOWER(last_name) for this query, but that would mean the database has to maintain two indexes for each insert, update, and delete statement (see also Chapter 8, “Modifying Data). To make one index suffice, you should consistently use the same function throughout your application.

Tweet this tip

Tip

Unify the access path so that one index can be used by several queries.

Warning

Sometimes ORM tools use UPPER and LOWER without the developer’s knowledge. Hibernate, for example, injects an implicit LOWER for case-insensitive searches.

Tweet this tip

Tip

Always aim to index the original data as that is often the most useful information you can put into an index.

If you like my way of explaining things, you’ll love my book.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

3
votes
1
answer
226
views

pagination with nulls

Oct 15 at 15:40 Lukas Eder 16
pagination
2
votes
1
answer
1.8k
views
0
votes
2
answers
1.1k
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 771
oracle index update