My webhost suspended my account because of mysql overload triggered by my blog. They requested me to look at the slow queries and repair the problem by "indexing" them, however i can't quite know very well what must i do here:

# Query_time: 1.116245  Lock_time: 0.000202 Rows_sent: 10  Rows_examined: 3486
use mydbname
select tag, t.tag_id, count(p2t.post_id) as count, ((count(p2t.post_id)/1070)*100) as weight, ((count(p2t.post_id)/109)*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 < '2011-12-06 09:00:01'
  AND (post_type = 'post')
  group by t.tag
  order by weight desc
  LIMIT 10

# Tue Dec  6 02:00:08 2011
# Query_time: 6.926785  Lock_time: 1.731793 Rows_sent: 10  Rows_examined: 3486
use mydbname
select tag, t.tag_id, count(p2t.post_id) as count, ((count(p2t.post_id)/1070)*100) as weight, ((count(p2t.post_id)/109)*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 < '2011-12-06 09:00:01'
  AND (post_type = 'post')
  group by t.tag
  order by weight desc
  LIMIT 10

I'd appreciate any help.

Thanks!

Before you decide to do other things, upgrade to the newest version of WordPress and import your UTW tag structures to WordPress's built-in term architecture should you upgrade to Wordpress 3.x before doing the import you will need to make use of an import wordpress plugin like this one. Will still be not the best sql I have seen, but it is cleaner compared to UTW tag sql, which wasn't internal to WordPress.

I'd generally accept @Nameless's point #1, with the exception that since you are seeing these queries create problems in your blog I imagine you are while using UTW tag functionality, and you will need to migrate the tag structure from UTW to native WordPress terms before you deactivate UTW, or you are prone to lose features you depend on.

Unless of course you are getting other, unrelated to UTW, MySQL issues, I wouldn't suggest attempting to change to InnoDB unless of course you've a really high traffic site I do not think the win from switching table types will probably be well worth the discomfort of doing this.

You actually can't do much, because the code producing this queries and database structure aren't yours, and spinning wordpress would take several weeks of labor.

But there's a couple of steps you can take.

  1. Problem queries contain wordpress_post2tag table, which goes to not wordpress itself, but to Ultimate Tag Warrior wordpress plugin. Maybe removing or crippling this wordpress plugin may help.
  2. You can test to upgrade wordpress and all sorts of plug ins to latest version, maybe problem had been solved.
  3. Automatically Wordpress uses not-so-optimal but avaiable everywhere MyISAM tables. You can test emigrate your database to InnoDB tables. But I wouldn't advise doing the work, without having strong grip how mysql works.

You'll need you utilize Show see plan of the query and it'll demonstrate so what can be enhanced. I suspect the issue is with group and order parts.