September 11, 2003
Battling with MySQL Indexes
Over the past week I got a stream of messages from users indicating that on one of the sites I admin there were some problems on certain pages. Of course I didn't have any details of the exact problem or which pages were failing so I started from scratch.
I intially thought the problem might be tied to lack of hardware (a bare-bones machine), but after setting up Zabbix it became clear that there was ample resources.
Spent an evening using ab to load-test pages and discovered most pages were normal except one, which pulls data from MySQL. I turned on MySQL's slow-query and found a multi-table join that was having problems. Went right to checking the indexes, which came up looking right with show index <table name>, but an explain <query> returned information indicating that the appropriate indexes weren't being used in the multi-table join. I couldn't figure out why the indexes weren't getting used.
After trying to decipher what the explain was demanding and failing multiple times I decided to show index on the individual tables, alter table drop index <index name> for each index and alter table add index (<column name>) for each column I wanted indexed. If I used syntax like alter table add index (<column name>,<column name>,...) I ended up getting several indexes all with the same name, which would create problems in my explain.
There is probably a good explanation for the index creation issue, but I found that creating indexes separately got rid of my slow query and reduced the page load times (under heavy load) from 59 sec to 1.2 sec.
I love indexes, especially when they work correctly.
Posted by mike at September 11, 2003 11:38 PM