2012-03-13Over-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.

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.