You know that… you are running a couple of applications on your web server and ONE just brings down everything with it's low quality sql statements…

To identify these bad statements, just follow these 5 simple steps and you can get performing queries in seconds…

STEP 1: Install MYTOP from Jeremy Zawodny's

MYTOP a great top clone for MySql – mytop ... an essential tool for every linux / mysql server running a couple of databases…

The Readme for installation should help you once you have downloaded MyTop Version 1.4

STEP 2: run it and wait for high load

be sure to press the button 'o' to reverse the sort order to have long running queries at the top

STEP 3: explain a long running statement

well – I found this by accident today… that's an undocumented feature that let's you create an explain statement for the whole query – and with it also the full query is displayed… very helpful if your statement always start with SELECT col1,col2,col3 etc.etc… and thereby all look the same from the standard view.

STEP 4: tune the SQL statement

well – cannot go much into detail here… but what the column "extra" for the following word:

"using temporary" – this mean the temporary segement is used for sorting… this is usually done via disk – eliminate this if possible

"using filesort" – well figure what this mean? Disk I/Os… 100 times more expensive than normal memory operations…

Of course there are a lot more things to take care of when optimizing your sql statements (not to mention the server configuration) ... if you are new to query optimization or looking for some new tricks to try, then you might want to checkout this blog dedicated to mysql performance tuning only

STEP 5: fix your application and continue with step #3

If you think just adding more CPUs and memory to your servers to avoid application tuning, then you are still wrong… too many bad apps out there hog up your resources… a well tuned application scales so much better than a wrong tuned…

This was true in 1996 when one of the biggest Oracle DBs had 700 Gigabytes… and is still true ten years later in 2006 for even a small 20-30 megabyte database on Mysql…