[development] Database queries on drupal.org

[Moshe Weitzman]

> 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

yikes. a big query inside a theme function. FIXME

> 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

the DISTINCT looks unneeded here. also, my node_comment_statistics patch 
removes a JOIN for this - http://drupal.org/node/75122. not sure who is 
issueing this query ... we should have some special case for pager_query() 
so we know who really issued it.

> 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

we are missing an index on uid here. i think that would help. node has such 
an index

> 0.614    0.614    1    eval    select message, count(message) as count 
> from watchdog where type = S and uid = D

whats issueing this query? looks odd. our indexes are not setup for this

> 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

an index on total_time might help here

if people generally agree with these, i will submit patches.