- About Optimizer Hints
- August 12–22: Online Training (EU shift)
- Clustered Indexes Dirty Secret
- Didn’t you know?
- FOSDEM Impressions
- Finding All the Red M&Ms
- June 8–18: Online Training (US shift)
- MySQL is to SQL like ??? to NoSQL
- Non-monetary micro sponsoring
- November 11-12 in Frankfurt am Main
- Online-Training in July and August
- Oracle + PostgreSQL
- Oracle Bootcamp
- PDF Registration
- Party time
- PostgreSQL Performance Event
- Quiz Results
- SQL Server Performance Kurs in Stuttgart
- SQL Server performance training in London
- SQLite learnings from PgCon
- Shipping Terms
- Springtime for SQL
- The two top performance problems caused by ORM tools
- Top Tweets January 2013
- Training Survey
- Training and Conference Dates
- Use The Index, Luke
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.
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
Unify the access path so that one index can be used by several queries.
Sometimes ORM tools use
LOWER without the developer’s knowledge. Hibernate, for example, injects an implicit
LOWER for case-insensitive searches.
Tweet this tip
Always aim to index the original data as that is often the most useful information you can put into an index.