Tag Archives: SQL

MySQL Style Guide

I’m a big believer in readability, and I love to write code that is easy to understand. Check out my newly-posted MySQL Style Guide which covers:

  • Simple queries
  • Complex queries
  • Subqueries
  • UNIONS
  • Advanced queries

Useful tips:

  • Use table aliases sparingly and only when absolutely necessary (a few extra keystrokes now will save you hours of gazing at cryptic queries later)
  • CAPITALIZE keywords religiously
  • Whitespace and tabs are your friends!

What to do when MySQL doesn’t use an index…

Sometimes MySQL doesn’t use the index on a column when performing a query.

Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:

  • SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

So what do you do in that case? This was my query:

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1 OR (`leadId` IS NULL AND `campaignId` = 8 AND `xid` = 'AAA100000000148')
ORDER BY `dispId` DESC
LIMIT 1

In this table (~175,000 rows), leadId, campaignId, and xid all have indexes, but MySQL was doing a table scan (~2.2 sec) because of the duplicate use of leadId and the way the WHERE clause was structured. This rather unusual optimization was more than 10x faster (~.17 sec):

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `campaignId` = 8 AND `xid` = 'AAA100000000148' AND `leadId` IS NULL

UNION ALL

SELECT dispId, outcome, contacted, householder, time_stamp
FROM `dispositions`
WHERE `leadId` = 1

ORDER BY `dispId` DESC
LIMIT 1

An odd case where two queries are faster than one!