I’m Mike

I was just looking over some statistics for the back-end MySQL database for this site and another project I’m involved with. I was impressed to discover the server had executed well over 100,000,000 queries since it started 68 days ago (well, 118,565,844 to be exact). That’s over twenty queries per second, for more than two months, without a hitch. Talk about enterprise level. Read the rest of this entry »

I just read a blog post discussing mysql query optimization and thought I’d put in my two cents.

The post suggests using a number of mysql specific statements (e.g. SQL_SMALL_RESULT, HIGH/LOW_PRIORITY, and INSERT DELAYED. STRAIGHT_JOIN was conspicuously missing). Unless absolutely necessary, this is usually A Bad Idea for at least two reasons. First, they are specific to MySQL which makes your database code less portable. This might or might not be a problem. Second, and perhaps more importantly, giving the SQL interpreter this sort of hint can lead to decreased performance in the future when your database or the interpreter changes. Telling the interpreter to anticipate a small result set (with SQL_SMALL_RESULT) might seem like a good idea, but could lead to problems when your table grows and the result becomes large! Basically, use these keywords with caution, and only when you really need them. And when you do use them, take special care in documenting where and why they’re in use.

The truth is there is no silver bullet that is going to make MySQL (or any dbms) run a poorly written query lightning fast. But here are some tips that the post somehow neglected to mention.

Properly index your tables

If you do a lot of lookups using a particular column of a table, or if you join on a column, that column should be indexed. Moreover, if all of the data that you are retrieving is available in the index (e.g. you’re using a multi-column index) then MySQL can avoid looking at the table altogether and execute your query using just the index.

Avoid superfluous queries

Don’t do this:

$result = query_db('select * from table1');

for each $result as $row
  $array[] = query_db('select * from table2 where column = '.$row['id']);
endforeach;

Do this:

$result = query_db('select table2.* from '
       .'table1, table2 where table1.id=table2.column');

Look for bottlenecks

Don’t waste time optimizing queries that aren’t bottlenecks in your application. Find the low hanging fruit and correct those problems first.

Learn SQL

This is the most important tip. SQL optimization really has to be done on a case by case basis, and you can’t do it unless you have a good understanding of the language and how you can use it to your advantage. You need to understand things like subqueries, grouping, left joins vs. right joins vs. full joins, etc. There is no free lunch.

If you’re interested in learning more, I highly recommend Stephane Faroult’s book The Art of SQL.