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.)
Improving MySQL performance on case-insensitve searches
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:
With MySQL, and most other databases, using
UPPER()
on a column prevents the database from using indexes, so even iflast_name
is indexed, in this case the database will do a slow full table scan. What I learned this morning is thatLIKE
, by default, is case-insensitive. While I generally associateLIKE
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:Now I get to take advantage of the index on
last_name
. (The query went from running in 11 seconds to .06 seconds.)Commentary
Previous post
Taking it slowNext post
Concurrency ahead