MySQL: avoid functions in WHERE clauses

One of the primary rules of MySQL Performance Optimization is to avoid using functions in a WHERE clause comparison when comparing an indexed column to a constant value. In other words, using indexed_column_name = SOME_CONSTANT is good but function(indexed_column_name) = SOME_CONSTANT is bad. MySQL will be unable to utilize the index on the column even if the function is very simple. The same applies to ORDER BY if you want to utilize an indexed column for sorting. Here's a real-world example. I have a table of user data with over 750K records in it. Take the following query where email is an indexed column.

SELECT * FROM users WHERE email = 'estenger@digiknow.com'

The query takes less than 0.1 seconds to run. Now let's examine the same query where I apply the LOWER function to the email column in the WHERE clause.

SELECT * FROM users WHERE LOWER(email) = 'estenger@digiknow.com'

This query takes a whopping 15+ seconds - over 150 times slower! I should also note that MySQL string comparisons are case-insensitive by default so applying the LOWER function is completely unnecessary here. Only use functions in a WHERE clause when absolutely necessary and if no other alternative is available.