WebDevelopment, ColdFusion, Railo, JS, Database and Tech-related by the Co-Founder and CEO of tunesBag.com

Sunday, 25 January 2009

Enhance performance of Joins / Queries: Make sure all columns share the same collation

At tunesBag.com, we're storing most of the data in UTF-8 / Unicode because we've customers from all over the world. Some internal data like userkeys, entrykeys etc are stored in latin1 because it needs less space. I recently came accross a query which was very slow without any obvious reason and after some research I found out that in a left join, the column A from table A has a different collation than column B from table A.
I changed the collation for column B to the same as column A - and the query executive time dropped from about 1300 msec to 100 msec!

So always make sure you're using the same collation if possible when performing joins!

No comments: