Troubleshooting a slow query

Question

I've taken over a Drupal 7 website, and i'm having trouble with a slow query that is run when an editor visits the /admin/content page. The query in question is the following:

SELECT COUNT(*) AS expression
FROM
(SELECT DISTINCT node.nid AS nid, node.title AS node_title, history.timestamp AS history_timestamp, node.created AS node_created, node.changed AS node_changed, node.type AS node_type, users_node.name AS users_node_name, users_node.uid AS users_node_uid, node.status AS node_status, 1 AS expression
FROM
node node
LEFT JOIN users users_node ON node.uid = users_node.uid
LEFT JOIN taxonomy_index taxonomy_index ON node.nid = taxonomy_index.nid
LEFT JOIN taxonomy_term_data taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
LEFT JOIN history history ON node.nid = history.nid AND history.uid = '1') subquery;

Edit: I've just managed to solve this issue, but there is a more general question:

How can a drupal 7 administrator identify the cause of errant queries to stop them from occurring?

Answer 1

To identify:

1. Install the Devel module
2. Visit admin/config/development/devel, enable the "Display query log" option.
3. At the bottom of every page you'll see a log of the queries run for the page, and what module and function has been responsible for invoking them. Run time of each query is also listed so you can see where the problems are.

As for stopping them, that will depend entirely on the individual query; whether you need it, whether the bottleneck is simply a matter of weight of data, poorly designed tables (contrib or custom, core tables are fine), not enough system resources, bad caching strategy, and so on. You'll need to profile that in the context of your web server for it to make any sense, there's no recipe for it.