Wishlisting Blog

After doing some work with the efficient_sql plugin for Rails, I discovered a small optimization that a lot of sites could probably benefit from but didn’t know existed.

Unless you’re using a binary collation on your database tables, your indexes are actually case insensitive. (ex. we use utf8_general_ci instead of utf8_bin in MySQL). How can you use this for an optimization? Well if you end up building a site with readable URLs, odds are you’ll put a username in them, like:

http://wishlisting.com/#person/tlianza

For usability, odds are you’ll want to make your URLs case-insensitive, in case someone created a username like Tlianza and someone else tries to navigate to it with tlianza. So, you end up writing a SQL query that looks something like this (ignoring the appropriate SQL injection cleansing for the purposes of a simple example):

SELECT * FROM users WHERE LOWER(user_name)='username_they_entered_lowercased'

Assuming you put an index on the user_name column (which you probably did since you’re going to query on it a lot) by wrapping it in a LOWER you’re actually not using your index anymore, as you’re performing a transformation on what would be the index hash/lookup key. Just take the LOWER out. You’ll be able to use your indexes again, and they’ll remain case insensitive.