Gerhard Killesreiter wrote:
Since last night, I let the devel module collect query statistics on drupal.org. We now can see which queries are run very often and which are just slow.
I have now stopped the data collecting.
Here are the first queries by accumulated time. The first column is accumulated time, the second average time/query, the third the number of times the query was run, the fourth the function which calls the query, the last the query itself:
927.011 0.005 192603 cache_get SELECT data, created, headers, expire FROM cache WHERE cid = S 905.582 0.001 973355 drupal_lookup_path SELECT dst FROM url_alias WHERE src = S 883.059 0.098 8986 theme_forum_topic_navigation SELECT n.nid, n.title, n.sticky, l.comment_count, l.last_comment_timestamp FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid AND r.tid = D 388.902 0.014 28330 node_load SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = D 186.896 2.967 63 pager_query SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = D 180.948 0.007 26472 cache_set INSERT INTO cache (cid, data, created, expire, headers) VALUES (S) 157.065 0.022 6983 node_load SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = S
I am not sure why there are two node_load queries which look identical otherwise. Probably these fields need to be merged.
Here again the first six queries: 12585.885 0.01 1322443 cache_get SELECT data, created, headers, expire FROM cache WHERE cid = S 7524.278 0.001 6814856 drupal_lookup_path SELECT dst FROM url_alias WHERE src = S 6062.889 0.107 56508 theme_forum_topic_navigation SELECT n.nid, n.title, n.sticky, l.comment_count, l.last_comment_timestamp FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid AND r.tid = D 4343.435 0.024 183059 node_load SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = D 1837.531 0.04 45429 node_load SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = S 1538.485 0.011 143889 cache_set INSERT INTO cache (cid, data, created, expire, headers) VALUES (S)
Here are the first few queries by average query time, columns as above.
155.447 3.048 51 pager_query SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D 186.896 2.967 63 pager_query SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = D 94.058 1.844 51 pager_query SELECT COUNT(DISTINCT(n.nid)) FROM node n LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D 109.691 1.155 95 pager_query SELECT DISTINCT m.*, r.*, u.name, u.uid FROM cvs_messages m INNER JOIN cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT D 1.03 1.03 1 pager_query SELECT a.aid, a.timestamp, a.url, a.uid, u.name FROM accesslog a LEFT JOIN users u ON a.uid = u.uid WHERE a.path LIKE S ORDER BY a.timestamp DESC LIMIT D 2.536 0.845 3 comment_edit SELECT c.*, u.uid, u.name AS registered_name, u.data FROM comments c INNER JOIN users u ON c.uid = u.uid WHERE c.cid = D 0.614 0.614 1 eval select message, count(message) as count from watchdog where type = S and uid = D 0.434 0.434 1 aggregator_page_sources SELECT f.fid, f.title, f.description, f.image, MAX(i.timestamp) AS last FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, f.description, f.image ORDER BY last DESC, f.title 0.429 0.429 1 pager_query SELECT q.qid, q.query, q.function, t.*, COUNT(t.qid) AS count, SUM(t.time) AS total_time, AVG(t.time) AS average, STDDEV(t.time) AS stddev FROM devel_queries q INNER JOIN devel_times t ON q.qid = t.qid GROUP BY t.qid ORDER BY total_time DESC LIMIT D
1343.749 3.262 412 pager_query SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = D 1045.001 2.969 352 pager_query SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D 628.091 1.784 352 pager_query SELECT COUNT(DISTINCT(n.nid)) FROM node n LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D 1091.909 1.327 823 pager_query SELECT DISTINCT m.*, r.*, u.name, u.uid FROM cvs_messages m INNER JOIN cvs_repositories r ON m.rid = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT D 19.668 1.093 18 eval select message, count(message) as count from watchdog where type = S and uid = D 147.989 1.08 137 cache_get DELETE FROM cache WHERE expire != D 1.03 1.03 1 pager_query SELECT a.aid, a.timestamp, a.url, a.uid, u.name FROM accesslog a LEFT JOIN users u ON a.uid = u.uid WHERE a.path LIKE S ORDER BY a.timestamp DESC LIMIT D 10.087 0.56 18 eval select message, count(message) as count from watchdog where type = S and uid != D 136.43 0.493 277 pager_query SELECT COUNT(DISTINCT(n.nid)) FROM node n INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid LEFT JOIN project_comments c ON c.nid = p.nid WHERE n.status = D 132.688 0.479 277 pager_query SELECT DISTINCT(n.nid) FROM node n INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid LEFT JOIN project_comments c ON c.nid = p.nid WHERE n.status = D 1247.877 0.473 2640 do_search CREATE TEMPORARY TABLE temp_search_sids SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = D 0.933 0.466 2 aggregator_page_sources SELECT f.fid, f.title, f.description, f.image, MAX(i.timestamp) AS last FROM aggregator_feed f LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, f.description, f.image ORDER BY last DESC, f.title 0.429 0.429 1 pager_query SELECT q.qid, q.query, q.function, t.*, COUNT(t.qid) AS count, SUM(t.time) AS total_time, AVG(t.time) AS average, STDDEV(t.time) AS stddev FROM devel_queries q INNER JOIN devel_times t ON q.qid = t.qid GROUP BY t.qid ORDER BY total_time DESC LIMIT D 9.767 0.425 23 do_search CREATE TEMPORARY TABLE temp_search_sids SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid INNER JOIN term_node tn ON n.nid = tn.nid INNER JOIN users u ON n.uid = u.uid WHERE n.status = D 6.014 0.401 15 pager_query SELECT uid, access FROM users WHERE uid > D 0.372 0.372 1 pager_query SELECT COUNT(*) FROM accesslog a LEFT JOIN users u ON a.uid = u.uid WHERE a.path LIKE S 96.123 0.364 264 forum_get_forums SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid WHERE n.status = D 42.274 0.282 150 cache_clear_all DELETE FROM cache WHERE cid = S
The queries have been collected on every tenth page view, so you should multiply the count and total time values by ten for real values.
I will make the collected data available to interested parties. Cheers, Gerhard