[development] Database queries on drupal.org

[Gerhard Killesreiter]

Hi there!

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.

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 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

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.

Cheers,
	Gerhard