Over the last week or so I’ve been on a mission to improve the performance of my web server, and especially MySQL. I took Arne’s advice and turned on the query cache. That helped but I still needed to do more. After doing some research I discovered MySQL’s slow query log, which does exactly what it sounds like. I enabled slow query logging and set “long_query_time” to 5 seconds. Shortly after I restarted MySQL the slow query count started to rise.
Every query in the slow query log was sent from the Ultimate Tag Warrior WordPress plugin which I use on my other blog. Here are some of the queries:
SELECT count( p2t.post_id ) cnt
FROM wp_tags t
INNER JOIN wp_post2tag p2t ON t.tag_id = p2t.tag_id
INNER JOIN wp_posts p ON p2t.post_id = p.ID
WHERE post_date_gmt < '2007-03-08 21:49:06' AND ( post_type = 'post' ) GROUP BY t.tag ORDER BY cnt DESC LIMIT 1 ;
and
SELECT tag, t.tag_id, count( p2t.post_id ) AS count, (
(
count( p2t.post_id ) /3661
) *100
) AS weight, (
(
count( p2t.post_id ) /1825
) *100
) AS relativeweight
FROM wp_tags t
INNER JOIN wp_post2tag p2t ON t.tag_id = p2t.tag_id
INNER JOIN wp_posts p ON p2t.post_id = p.ID
WHERE post_date_gmt < '2007-03-09 02:27:39' AND ( post_type = 'post' ) GROUP BY t.tag ORDER BY weight DESC LIMIT 50 ;
That led me to take a look at what was going on with the wp_tags and wp_post2tag tables. I did EXPLAINs on the queries and saw that they were doing table scans instead of using the indexes. So I went to look at the table definitions and was surprised at what I saw. The only index on the wp_post2tag table was rel_id, the auto-incremented primary key. So the columns that were actually used to do joins with, tag_id and post_id, had no indices. My SQL is very rusty but I knew that wasn’t a good thing. I also took a look at the wp_tags table and saw that it only had an index on the tag_id column. I’ve seen some queries with “tag = ‘tag_name’ ” in the WHERE clause so I figured that it would be good to have an index on the tag column as well.
After consulting with my brother, whose SQL skills are much more up to date than my own I decided to add indexes to those tables. I created an index called ‘tags_tag_idx’ on the wp_tags.tag column. On the wp_post2tag column I created two indexes — the post2tag_tag_post_idx index is on tag_id then post_id and the post2tag_post_tag_idx index is on post_id then tag_id. I’m not sure if using concatenated indexes is better than just creating separate single column indexes for each column but I think it’s the way to go after discussing with my brother and looking at how the wp_post2cat and wp_linktocat tables are indexed. They both have concatenated indices.
I ran some queries on the tables before and after to see if things were sped up and indeed they were. Unfortunately when I ran the EXPLAIN on the queries in the slow query log I saw mixed results. The keys that I added were now showing up as “possible_keys” and the actual keys but the queries still ended up doing table scans. For the tags table the EXPLAIN shows the dreaded “Using temporary; Using filesort”.
So while I didn’t completely solve my slow query problem the new indexes do help for many of the simpler queries which access wp_post2tag and wp_tag. If you’re using Ultimate Tag Warrior and are concerned about your database load you may want to add some indexes to the tag tables.