Logging ToolkitIf you have a slow loading page or test and have no idea which one of the thirty or so queries going on is the cause how to do you find it. Slow query log and logging queries that are not using indexes. Adding the following to your my.ini, my.cnf or whatever your mysql config file is named on your system and you’ll be able to get a list of offenders to further investigate.
MySQL Console ToolkitSHOW INNODB STATUS – gives tons of useful information on what the current state of deadlocks, transactions and all sorts of information.
SHOW FULL PROCESSLIST – shows the current threads and queries running on them. running this over and over again can show you queries that are hanging.
EXPLAIN <QUERY>– Just in place of query type your full query statement, particularly in sub-queries, this is great for pointing out potential problems, in my most recent case I had an uncacheable sub-query, pointing out quickly something that was just not going to work no matter what.
Final NotesAnyway this is only a smidgen of the useful stuff you need in your toolkit and I’m certainly no expert. I want to plug High Performance MySQL: Optimization, Backups, Replication, and More and the http://www.mysqlperformanceblog.com/ for it’s help and as a reference for those wanting to go into deeper study.
Note: Cross posted from Polyglots R Us.