melfneerg.com

- 'cos life is like that

About the Author
Tudor Davies

author Tudor is a techie turned manager who fights like mad to keep his tech skills honed and relevant. Everything from web hosting, networking, *nix and the like. Constantly developing and co-ordinating with others to make the web a better (and easier to use) place.

mySQL slow queries

Tuesday, 14th Feb 2012 Posted @ 23:05

So I was wondering how well my music database was performing, so I added a few entries to my /etc/my.cnf:

log_slow_queries=/var/log/mysqld.slow.log
long-query-time=1
log-long-format
log-queries-not-using-indexes

I then checked the mysqld.slow.log file to see what was going on, as it was now logging queries that take over a second and queries that are not using an index.
This identified several queries that need attention, so a quick EXPLAIN of the queries showed me what was going on.
Most of my tables had a PRIMARY KEY (based on the auto-incrementing ID field) but no indexes of the data itself. After a couple of

ALTER TABLE blah ADD INDEX blahcolumn (column)

commands I tested again. None of the queries were being logged and they were executing a damn sight faster. Cutting down the return of a 500 row table by 40% was a decent enough improvement :)

Now I need to do the same to the mySQL server in work and see if I can improve the responsiveness of our intranet applications...

[ no comments : Add ]

start

layout and initial css based on the Qtractor page