[development] Question on tracker query

[Narayan Newton]

Missed that join and then noticed that this query is actually gone on
drupal.org, replaced with a UNION that is far better. Thanks.

On Fri, 2007-08-03 at 13:13 -0700, Earl Miles wrote:
> 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.