There are countless things that I dislike about MySQL. One thing that’s impressing me is that in 4 hours, our MySQL server for one of our major customers has performed 700 million row reads. The interesting bit is that a full half-billion of those hits can be eliminated. Using the MySQL slow query log with a 1 second threshold and an enhanced version of the MySQL slow query log parser, combined with ‘mysqladmin extended-status’, I was able to track our database performance issues to an isolated set of 3 queries that are executed regularly and require full table scans. Adding 3 single column integer indexes on 150k-200k row tables should solve that and cut our reads at least in half.
Advertisement
Determining a better way to index a table always makes me smile. The performance gains can be immense.
Further reinforces the fact that 80% of the problems are caused by 20% of the code…. approximately.