Let’s say you have a database that contains some strings that you want to search, like last names. This table accepts data entered via the Web, so someone might enter their last name as ‘smith’ or ‘Smith’ or ‘SMITH’ or even ‘smitH’. There’s no way to tell. When people search for the last name, they might do exactly the same thing. Bottom line — you need to do a case-insensitive search. In every application I’ve ever written, I’ve written the query like this:

SELECT last_name FROM people WHERE UPPER(last_name) = UPPER('smith')

With MySQL, and most other databases, using UPPER() on a column prevents the database from using indexes, so even if last_name is indexed, in this case the database will do a slow full table scan. What I learned this morning is that LIKE, by default, is case-insensitive. While I generally associate LIKE with ignoring indexes, if the expression doesn’t begin with %, MySQL will use an index if it’s available. So I rewrote the query above like this:

SELECT last_name FROM people WHERE last_name LIKE 'smith'

Now I get to take advantage of the index on last_name. (The query went from running in 11 seconds to .06 seconds.)