How not to manage a MySQL query

Poorly written queries will run slowly in MySQL. There’s really no way around it, but a slew of posts over at Digg.com are trying anyway. One blogger named Mike that I found today takes them all to task with a few tips on the proper way to handle this sticky SQL situation:

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.

Lunch… hmm. What time is it? MySQL queries make me hungry — but only if they’re well written!

Also, are you new to MySQL? Check out SearchEnterpriseLinux.com’s recent technical tip on Getting Started with MySQL.