Small DB optimization for your apps
January 22nd, 2008
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.
February 17th, 2008 at 10:53 am
Yuck, don’t do that, use a real database (Postgres) and create a functional index which includes the lower(xxxxx).
August 17th, 2012 at 8:28 pm
These Oakley Antix Sunglasses are excellent price tag as well as fashion look fantastic.The lens of cheap oakley sunglasses for sale is huge, supplying wonderful protection, and allowing you still see every little thing you need devoid of unsightly frame during the way. Super stylish shades, very simple but sophisticated.Just one constant lens of pure Plutonite? strafes the entrance of the light-weight O Matter frame, and Polaric Ellipsoid geometry allows oakley outlet condition it to extend your peripheral watch. The Oakley Antix Sunglasses make it easier to blur the line involving avenue and sleek.
September 25th, 2012 at 7:33 pm
As of today north face outlet stores is presently based in San Leandro, California near its corporate sibling the JanSports Company who was now recognize as the worlds largest backpack maker that supplies small backpack in many of north face sale clearance retailer stores in most of the places in the united states together with their sister company which is the north face.
November 9th, 2012 at 1:05 am
Dsquared clothes have recently taken the fashion world by storm. In the dsquared outlet online store, you can find the innovative and fresh designs dsquared shoes and clothing. Luckily, in this section of north face outlet shop, we have assembled a great selection of north face jackets and accessories. Feel free to browse and choose what suits you best.
February 17th, 2013 at 8:04 pm
2013 spring comes,welcome to hollister outlet hollister outlet to buy new style clothes.All of the clothes show in hollister outlet are brand new with high quality.If you love bags,never miss longchamp outlet longchamp outlet online store.You will find all kinds of things you like online.