User list of projects committed to should not include sandbox projects unless the user is the maintainer

So here's the query that I came up with, not sure if it's ideal:

SELECT DISTINCT(vcp.nid), n.title, COUNT(o.vc_op_id) AS commits FROM versioncontrol_operations o INNER JOIN versioncontrol_project_projects vcp ON vcp.repo_id = o.repo_id INNER JOIN node n ON n.status = 1 AND n.nid = vcp.nid INNER JOIN project_projects pp ON n.nid = pp.nid WHERE o.author_uid = 1 AND (pp.sandbox = 0 OR (pp.sandbox = 1 AND n.uid = 1)) GROUP BY vcp.nid, n.title ORDER BY commits DESC

Here's the explain when run on git-dev.drupal.org:

mysql> EXPLAIN SELECT DISTINCT(vcp.nid), n.title, COUNT(o.vc_op_id) AS commits FROM versioncontrol_operations o INNER JOIN versioncontrol_project_projects vcp ON vcp.repo_id = o.repo_id INNER JOIN node n ON n.status = 1 AND n.nid = vcp.nid INNER JOIN project_projects pp ON n.nid = pp.nid WHERE o.author_uid = 1 AND (pp.sandbox = 0 OR (pp.sandbox = 1 AND n.uid = 1)) GROUP BY vcp.nid, n.title ORDER BY commits DESC;
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+------------+---------+-------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys                                                                     | key        | key_len | ref               | rows  | Extra                           |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+------------+---------+-------------------+-------+---------------------------------+
|  1 | SIMPLE      | o     | ref    | repo_id,author_uid                                                                | author_uid | 4       | const             | 17250 | Using temporary; Using filesort | 
|  1 | SIMPLE      | vcp   | ref    | PRIMARY,repo_id                                                                   | repo_id    | 4       | git_dev.o.repo_id |     1 | Using index                     | 
|  1 | SIMPLE      | pp    | eq_ref | PRIMARY                                                                           | PRIMARY    | 4       | git_dev.vcp.nid   |     1 | Using where                     | 
|  1 | SIMPLE      | n     | eq_ref | PRIMARY,uid,node_status_type,nid,tracker_user,tracker_global,node_status_type_uid | PRIMARY    | 4       | git_dev.vcp.nid   |     1 | Using where                     | 
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+------------+---------+-------------------+-------+---------------------------------+
4 rows in set (0.00 sec)

status

Well there are some other problems with this listing if we want to fix it up right while we're at it, namely that it isn't paged and for folks like dww, and a few others it's quite slow to load. I'd really like to see this be a short list of top 100 maybe, with more link linking off to another paged view of projects (maybe load the more by ajax?).

Given how slow this query is we should also maybe think about caching similar to the maintainers block as well?

Committed.