WordPress MU

What is the next step I should take to figure out what causes the slow queries on this report?

According the my server administrator, I have a problem with slow queries causing my pages to wait to load while the server is running a query that takes 37 seconds.

Within just a few minutes of having the problem, the pages started loading within 2 seconds again. This happens frequently enough to be a problem.

Here is the log the admin retrieved for me while the site was running slow. Does this help shoot the trouble or should I look somewhere else?

Here are the queries I was showing as taking a long time - I was seeing about
36 seconds until they completed.

root@host [~]# mysqladmin proc
+---------+-----------------+-----------+-----------------------+---------+------+--------------------+---
---------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | In
fo |
+---------+-----------------+-----------+-----------------------+---------+------+--------------------+---
---------------------------------------------------------------------------------------------------+
| 2142632 | funcity_funcity | localhost | funcity_funcityfinder | Query
| | 36 | Creating tmp table | SE
LECT t.*, tt.*, tr.object_id FROM wp_139_terms AS t INNER JOIN wp_139_term_taxonomy AS tt ON tt.te |
| 2142642 | funcity_funcity | localhost | funcity_funcityfinder | Query
| | 34 | Creating tmp table | SE
LECT t.*, tt.*, tr.object_id FROM wp_79_terms AS t INNER JOIN wp_79_term_taxonomy AS tt ON tt.term |
| 2142653 | funcity_funcity | localhost | funcity_funcityfinder | Query
| | 31 | Creating tmp table | SE
LECT t.*, tt.*, tr.object_id FROM wp_79_terms AS t INNER JOIN wp_79_term_taxonomy AS tt ON tt.term |
| 2142667 | funhome_funhome | localhost | funhome_funhomefinder | Query
| | 21 | logging slow query | SE LECT option_name, option_value FROM
| wp_42_options WHERE autoload = 'yes' | 2142670 | funcity_funcity |
| localhost | funcity_funcityfinder | Query | 27 | Updating | UP DATE
| wp_135_options SET option_value = '1264274834' WHERE option_name
| = '_transient_doing_cr |
| 2142681 | funcity_funcity | localhost | funcity_funcityfinder | Query
| | 19 | Updating | UP DATE wp_125_options SET option_value =
| '1264274842' WHERE option_name = '_transient_doing_cr | 2142688 |
| funhome_funhome | localhost
| | funhome_funhomefinder | Query | 16 | Updating | UP DATE
| | wp_40_options
| SET option_value = '1264274845' WHERE option_name =
| '_transient_doing_cro | 2142693 | funcity_funcity | localhost |
| funcity_funcityfinder | Query | 15 | Updating | UP DATE
| wp_37_options SET option_value = '1264274846' WHERE option_name
| = '_transient_doing_cro |
| 2142703 | funcity_funcity | localhost | funcity_funcityfinder | Query
| | 11 | Creating tmp table | SE LECT t.*, tt.*, tr.object_id FROM
| wp_79_terms AS t INNER JOIN wp_79_term_taxonomy AS tt ON tt.term |
| 2142725 | funcity_funcity
| | localhost | funcity_funcityfinder | Query | 5 | Updating | UP DATE
| wp_169_options SET option_value = '1264274856' WHERE option_name
| = '_transient_doing_cr | 2142729 | funcity_funcity | localhost |
| funcity_funcityfinder | Query | 3 | Opening tables | SE LECT t.*,
| tt.*, tr.object_id FROM wp_79_terms AS t INNER JOIN
| wp_79_term_taxonomy AS tt ON tt.term | 2142740 | root | localhost | |
| Query | 0 | | sh ow processlist |

This might be a silly question but when did you last optimise your database?

The queries its having problems with are in the taxonomy.php file in wp-includes

are you using any tags or categories plugin, or running custom code that does things to tags?

If you've got a large number of taxonomies then check to see there is an index on the taxonomy column in the term_taxonomies table.

It looks like it wasn't there originally but has been added somewhere along the line. So if you are on 2.9.1 then it should be there but if you're still on an older version...