[development] Question on tracker query

Earl Miles merlin at logrus.com
Fri Aug 3 20:13:41 UTC 2007

Narayan Newton wrote:
> Not sure if this is the best place to ask this, but I'm sure I'll be
> informed quickly and forcefully if its not ;). I notice that one of the
> tracker queries, specifically:
>   $sql = 'SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid,
> u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated,
> 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 OR c.status IS NULL) WHERE
> n.status = 1 AND (n.uid = %d OR c.uid = %d) ORDER BY last_updated DESC';
> Is using a (in my opinion) rather dubious DISTINCT. Does this ever
> actually do anything? Given I am not very familiar with the codebase,
> but I at first glance at least...I just don't see it and its causing a
> filesort for this query. Thoughts? Corrections? Flames?...Rabbits?

The DISTINCT is preventing the query from returning X results per nid where X 
is the number of comments I have authored.

More information about the development mailing list