{"id":494,"date":"2007-03-10T19:29:06","date_gmt":"2007-03-10T23:29:06","guid":{"rendered":"http:\/\/tradermike.net\/movethecrowd\/archives\/2007\/03\/lack-of-indexes-on-ultimate-tag-warrior-tables\/"},"modified":"2007-03-10T19:29:06","modified_gmt":"2007-03-10T23:29:06","slug":"lack-of-indexes-on-ultimate-tag-warrior-tables","status":"publish","type":"post","link":"http:\/\/www.michaelseneadza.com\/blog\/2007\/03\/10\/lack-of-indexes-on-ultimate-tag-warrior-tables\/","title":{"rendered":"Lack of Indexes on Ultimate Tag Warrior Tables"},"content":{"rendered":"

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<\/a>. That helped but I still needed to do more. After doing some research I discovered MySQL’s slow query log<\/a>, 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. <\/p>\n

Every query in the slow query log was sent from the Ultimate Tag Warrior WordPress plugin<\/a> which I use on my other blog<\/a>. Here are some of the queries:<\/p>\n

\nSELECT count( p2t.post_id ) cnt
\nFROM wp_tags t
\nINNER JOIN wp_post2tag p2t ON t.tag_id = p2t.tag_id
\nINNER JOIN wp_posts p ON p2t.post_id = p.ID
\nWHERE post_date_gmt < '2007-03-08 21:49:06'\nAND (\npost_type = 'post'\n)\nGROUP BY t.tag\nORDER BY cnt DESC\nLIMIT 1 ;\n<\/p><\/blockquote>\n

and<\/p>\n

\nSELECT tag, t.tag_id, count( p2t.post_id ) AS count, (
\n(
\ncount( p2t.post_id ) \/3661
\n) *100
\n) AS weight, (
\n(
\ncount( p2t.post_id ) \/1825
\n) *100
\n) AS relativeweight
\nFROM wp_tags t
\nINNER JOIN wp_post2tag p2t ON t.tag_id = p2t.tag_id
\nINNER JOIN wp_posts p ON p2t.post_id = p.ID
\nWHERE post_date_gmt < '2007-03-09 02:27:39'\nAND (\npost_type = 'post'\n)\nGROUP BY t.tag\nORDER BY weight DESC\nLIMIT 50 ;\n<\/p><\/blockquote>\n

That led me to take a look at what was going on with the wp_tags and wp_post2tag tables. I did EXPLAINs<\/a> 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.<\/p>\n

After consulting with my brother<\/a>, 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. <\/p>\n

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”. <\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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… Continue reading Lack of Indexes on Ultimate Tag Warrior Tables<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","footnotes":""},"categories":[2,16],"tags":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":566,"url":"http:\/\/www.michaelseneadza.com\/blog\/2014\/05\/02\/calculating-standard-deviations-in-ruby-on-rails-and-postgresql\/","url_meta":{"origin":494,"position":0},"title":"Calculating Standard Deviations in Ruby on Rails (and PostgreSQL)","date":"May 2, 2014","format":false,"excerpt":"I need to calculate some Bollinger Bands (BBs) for SwingTradeBot, which is built in Rails 4. Here's a quick definition of Bollinger Bands: Bollinger Bands\u00ae are volatility bands placed above and below a moving average. Volatility is based on the standard deviation, which changes as volatility increases and decreases. So\u2026","rel":"","context":"In "Technology"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":379,"url":"http:\/\/www.michaelseneadza.com\/blog\/2004\/08\/23\/improving_users_search_experience\/","url_meta":{"origin":494,"position":1},"title":"Improving Users’ Search Experience","date":"August 23, 2004","format":false,"excerpt":"Here's an interesting look at some of the habits of users while searching for information online, complete with ideas on improving your site's search usability. Here are a few of the things that stood out to me (my comments are in italics): Satisfaction of both casual users and the more\u2026","rel":"","context":"In "Blogging"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":574,"url":"http:\/\/www.michaelseneadza.com\/blog\/2014\/08\/06\/slow-responses-from-the-braintree-ruby-gem-try-this-fix\/","url_meta":{"origin":494,"position":2},"title":"Slow Responses from the BrainTree Ruby Gem? Try This Fix.","date":"August 6, 2014","format":false,"excerpt":"A few weeks ago I was tasked with trying to mitigate some timeout issues in a client's Rails app making BrainTree calls. This was becoming more of a problem as the client's users built up more & more history in BrainTree. Apparently you can't paginate the results or ask BrainTree\u2026","rel":"","context":"In "Technology"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":371,"url":"http:\/\/www.michaelseneadza.com\/blog\/2004\/08\/05\/tips_on_tivoing_the_olympics\/","url_meta":{"origin":494,"position":3},"title":"Tips on TiVoing the Olympics","date":"August 5, 2004","format":false,"excerpt":"PVRBlog has a post with some excellent ideas and resources for catching those Olympic events you want to see. Maybe we'll be lucky and they won't broadcast the US Basketball team getting trounced. And they also have a post about the apparent slow death of the X Games. I just\u2026","rel":"","context":"In "Sports"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":377,"url":"http:\/\/www.michaelseneadza.com\/blog\/2004\/08\/19\/note_to_the_press_its_weblog_one_damn_word\/","url_meta":{"origin":494,"position":4},"title":"Note to the Press: It’s ‘Weblog’, One Damn Word!","date":"August 19, 2004","format":false,"excerpt":"[rant]This has been bugging the sh!t out of me for weeks now, ever since all the media coverage over the convention bloggers. Why is it so difficult for the mainstream media to understand that 'weblog' is one word? It's not \"web log\" or even worse \"web blog\" as I just\u2026","rel":"","context":"In "Blogging"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":470,"url":"http:\/\/www.michaelseneadza.com\/blog\/2005\/06\/13\/technorati_beta\/","url_meta":{"origin":494,"position":5},"title":"Technorati Beta","date":"June 13, 2005","format":false,"excerpt":"Check out the revamped (and well designed) Technorati. Here's what's new in the beta release: We've improved the user experience, making Technorati accessible to more people and, specifically, people who are new to blogging. We've tried to make it very simple to understand what Technorati is all about, and make\u2026","rel":"","context":"In "Blogging"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts\/494"}],"collection":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/comments?post=494"}],"version-history":[{"count":0,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/posts\/494\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/media?parent=494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/categories?post=494"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.michaelseneadza.com\/blog\/wp-json\/wp\/v2\/tags?post=494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}