Lack of Indexes on Ultimate Tag Warrior Tables

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.

My Top 20+ Movies

In answer to Trader X’s question, here are some of my favorite movies. There’s no way I can rank them beyond maybe the first three. Nor could I stop at just twenty so with the help of my historical rankings on NetFlix I’ve gone 40 deep. Depending on my mood, any of these could be in the top 20:

  • The Shawshank Redemption
  • City of God (Cidade de Deus)
  • Trading Places
  • A Fish Called Wanda
  • O Brother, Where Art Thou?
  • Clear and Present Danger
  • Friday
  • Malcolm X
  • The Matrix
  • The Devil’s Advocate
  • Rabbit-Proof Fence
  • Memento
  • Get Shorty
  • Rush Hour
  • Brown Sugar
  • The Sixth Sense
  • Austin Powers 1
  • There’s Something About Mary
  • Buena Vista Social Club
  • The Fifth Element
  • Desperado

Honorable Mention:

  • Training Day
  • The Usual Suspects
  • Misery
  • Pulp Fiction
  • Amistad
  • Analyze This
  • As Good as It Gets
  • Better Than Chocolate
  • Black Hawk Down
  • Blade
  • Casino
  • The Game
  • GoodFellas
  • Heat
  • Set it Off
  • Sling Blade
  • The Thomas Crown Affair
  • The Untouchables
  • Donnie Brasco

Archived for Posterity: Kenneth Eng’s ‘Why I Hate Blacks’ Article

Just thought I’d archive some (more) ignorance:

This is a copy of the controversial opinion piece by Kenneth Eng in Asian Week magazine:

Here is a list of reasons why we should discriminate against blacks, starting from the most obvious down to the least obvious:

• Blacks hate us. Every Asian who has ever come across them knows that they take almost every opportunity to hurl racist remarks at us.

In my experience, I would say about 90 percent of blacks I have met, regardless of age or environment, poke fun at the very sight of an Asian. Furthermore, their activity in the media proves their hatred: Rush Hour, Exit Wounds, Hot 97, etc.

• Contrary to media depictions, I would argue that blacks are weak-willed. They are the only race that has been enslaved for 300 years. It’s unbelievable that it took them that long to fight back.

On the other hand, we slaughtered the Russians in the Japanese-Russo War.

• Blacks are easy to coerce. This is proven by the fact that so many of them, including Reverend Al Sharpton, tend to be Christians.

Yet, at the same time, they spend much of their time whining about how much they hate “the whites that oppressed them.”

Correct me if I’m wrong, but wasn’t Christianity the religion that the whites forced upon them?

• Blacks don’t get it. I know it’s a blunt and crass comment, but it’s true. When I was in high school, I recall a class debate in which one half of the class was chosen to defend black slavery and the other half was chosen to defend liberation.

Disturbingly, blacks on the prior side viciously defended slavery as well as Christianity. They say if you don’t study history, you’re condemned to repeat it.

In high school, I only remember one black student ever attending any of my honors and AP courses. And that student was caught cheating.

It is rather troubling that they are treated as heroes, but then again, whites will do anything to defend them.

Here’s some follow up on the situation: Asian paper’s ‘I Hate Blacks’ column assailed