{"id":636,"date":"2010-01-11T21:52:01","date_gmt":"2010-01-12T03:52:01","guid":{"rendered":"http:\/\/antone.geckotribe.com\/alpha-gecko\/?p=636"},"modified":"2026-10-24T13:40:29","modified_gmt":"2026-10-24T19:40:29","slug":"optimizing-case-sensitive-queries-in-mysql","status":"publish","type":"post","link":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/optimizing-case-sensitive-queries-in-mysql\/","title":{"rendered":"Optimizing Case Sensitive Queries in MySQL"},"content":{"rendered":"<p>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:<\/p>\n<p><code>select foo_field from foo_table where binary foo_key=\"foo value\"<\/code><\/p>\n<p>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:<\/p>\n<p><code>select foo_field from foo_table where (foo_key=\"foo value\") && (binary foo_key=\"foo value\")<\/code><\/p>\n<p>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 <i>considerably<\/i> less records :-) using a case sensitive check. Schweet!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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...<br \/>[<a class=\"moretag\" href=\"https:\/\/zeta.geckotribe.com\/alpha-gecko\/optimizing-case-sensitive-queries-in-mysql\/\">read more<\/a>]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[116],"class_list":["post-636","post","type-post","status-publish","format-standard","hentry","category-technology","tag-mysql"],"_links":{"self":[{"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/posts\/636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/comments?post=636"}],"version-history":[{"count":1,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/posts\/636\/revisions"}],"predecessor-version":[{"id":930,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/posts\/636\/revisions\/930"}],"wp:attachment":[{"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/media?parent=636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/categories?post=636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zeta.geckotribe.com\/alpha-gecko\/wp-json\/wp\/v2\/tags?post=636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}