Open source MySQL performance tool

To profile a mysql command you could use SHOW PROFILE

mysql> SET profiling = 1;
mysql> [type your query here]
mysql> SHOW PROFILE;

and to get more information about what mysql would do in order to get your desired result, you could use EXPLAIN.

mysql> EXPLAIN [type your query here]

Good starting points for optimizations are the usage of indices and the increase of memory usage in my.cnf (the default parameters could be very low). If you delete a lot of data in a table OPTIMIZE TABLE could also be helpful.

OPTIMIZE TABLE table;

In any case you should use the output of SHOW PROFILE to determine which parts need a lot of time and are worth to be optimized.

Be aware to switch of the query cache while optimizing. You would trick yourself otherwise.

I think you should look at these blogs.

http://www.mysqlperformanceblog.com/