Optimizing Case Sensitive Queries in MySQL
by ZetaGecko | Add Your Comments | Technology
The other day, I ran into a situation where I needed to do case sensitive queries in MySQL. With a little Googling, I discovered that the keyword "binary" makes that possible, like this:
select foo_field from foo_table where binary foo_key="foo value"
So I tried it out and it worked, but when querying a table with over a million records, it was dog slow compared to a normal case insensitive query. Fortunately, a solution occurred to me which, as far as I can tell, makes the query run almost exactly as fast as a normal query:
select foo_field from foo_table where (foo_key="foo value") && (binary foo_key="foo value")
This tells MySQL to run the faster, case insensitive query first, and then narrow down the found set (which in a 1,000,000+ record table contains considerably less records :-) using a case sensitive check. Schweet!