Reduce left joins for issue views

We'll need Earl to comment here. I don't know what to do about that myself.

You know, Views produces formatted queries for a reason. Give me something I can read.

Ok, after reformatting it a little, I don't have much to actually offer; the JOIN type either by the relationship or by the join identifier. It defaults to LEFT because in general it can't know what the rest of the query is going to look like and INNER is more restrictive.

Relationships have a setting in the UI, so those can easily be flipped to INNER. Judging from the explain export (which I've edited slightly so it can be read) it looks like probably only the closest join to 'node' is really going to matter much, just flipping everything to INNER looks like it might be wasteful. But I guess you'd have to EXPLAIN each one to be sure.

The problem is that if you make the 'project_issues' join an INNER join then any time you have project issues fields in any view query anywhere, it will require that there are actually issues. Maybe that's acceptable in terms of the project, I don't know. That's something Derek will probably have to determine.

Earl: I never understood that's what the "Require this relationship" UI setting was about. ;) I take it there's no equivalent setting anywhere in the UI for the JOINs for fields, huh?

That said, I'd be willing to make all the project_issue tables INNER JOIN (at least for now), since I can't fathom issue views that don't require issue nodes. If someone can come up with a use case that would need LEFT, I'll reconsider, but for now, all of the default views obviously depend on issues, so I don't see any harm in requiring INNER.

Yes, that's the plan. I had to take almost all of last week off from work, but I'm back now... This will be one of my first tasks.

After some recent commits, the query for the project_issue_all_projects view now looks like this:

SELECT node.nid AS nid,
node_project_issues_1.title AS node_project_issues_1_title,
node_project_issues_1.nid AS node_project_issues_1_nid, node_project_issues_1__project_projects.uri AS node_project_issues_1__project_projects_uri,
node.title AS node_title,
project_issues.sid AS project_issues_sid,
project_issues.priority AS project_issues_priority,
project_issues.category AS project_issues_category,
node_comment_statistics.comment_count AS node_comment_statistics_comment_count,
node.type AS node_type,
node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp,
users_project_issues.name AS users_project_issues_name,
users_project_issues.uid AS users_project_issues_uid
FROM node node
INNER JOIN project_issues project_issues ON node.nid = project_issues.nid
INNER JOIN users users_project_issues ON project_issues.assigned = users_project_issues.uid
LEFT JOIN node node_project_issues ON project_issues.rid = node_project_issues.nid
INNER JOIN node node_project_issues_1 ON project_issues.pid = node_project_issues_1.nid
LEFT JOIN project_projects node_project_issues_1__project_projects ON node_project_issues_1.nid = node_project_issues_1__project_projects.nid
LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (node.type in ('project_issue')) AND (node.status <> 0 OR node.uid = ***CURRENT_USER*** or ***ADMINISTER_NODES*** = 1) AND (project_issues.sid in ('1', '8', '13', '14', '15', '2', '4'))
ORDER BY node_comment_statistics_last_comment_timestamp DESC

Which results in the following EXPLAIN:

mysql> EXPLAIN SELECT node.nid AS nid, node_project_issues_1.title AS node_project_issues_1_title, node_project_issues_1.nid AS node_project_issues_1_nid, node_project_issues_1__project_projects.uri AS node_project_issues_1__project_projects_uri, node.title AS node_title, project_issues.sid AS project_issues_sid, project_issues.priority AS project_issues_priority, project_issues.category AS project_issues_category, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node.type AS node_type, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, users_project_issues.name AS users_project_issues_name, users_project_issues.uid AS users_project_issues_uid FROM node node  INNER JOIN project_issues project_issues ON node.nid = project_issues.nid INNER JOIN users users_project_issues ON project_issues.assigned = users_project_issues.uid LEFT JOIN node node_project_issues ON project_issues.rid = node_project_issues.nid INNER JOIN node node_project_issues_1 ON project_issues.pid = node_project_issues_1.nid LEFT JOIN project_projects node_project_issues_1__project_projects ON node_project_issues_1.nid = node_project_issues_1__project_projects.nid LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE (node.type in ('project_issue')) AND (node.status <> 0 OR node.uid = 1) AND (project_issues.sid in ('1', '8', '13', '14', '15', '2', '4')) ORDER BY node_comment_statistics_last_comment_timestamp DESC;
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------------------+-----------+---------+---------------------------------+--------+----------------------------------------------+
| id | select_type | table                                   | type   | possible_keys                                                                               | key       | key_len | ref                             | rows   | Extra                                        |
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------------------+-----------+---------+---------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | node                                    | ref    | PRIMARY,uid,node_type,node_status_type,nid,tracker_user,tracker_global,node_status_type_uid | node_type | 32      | const                           | 114055 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | project_issues                          | eq_ref | PRIMARY,project_issues_pid,project_issues_sid,project_issues_nid_assigned                   | PRIMARY   | 4       | drupal6.node.nid                |      1 | Using where                                  | 
|  1 | SIMPLE      | node_project_issues                     | eq_ref | PRIMARY,nid                                                                                 | PRIMARY   | 4       | drupal6.project_issues.rid      |      1 | Using index                                  | 
|  1 | SIMPLE      | node_project_issues_1                   | eq_ref | PRIMARY,nid                                                                                 | PRIMARY   | 4       | drupal6.project_issues.pid      |      1 |                                              | 
|  1 | SIMPLE      | node_project_issues_1__project_projects | eq_ref | PRIMARY                                                                                     | PRIMARY   | 4       | drupal6.project_issues.pid      |      1 |                                              | 
|  1 | SIMPLE      | node_comment_statistics                 | eq_ref | PRIMARY                                                                                     | PRIMARY   | 4       | drupal6.node.nid                |      1 |                                              | 
|  1 | SIMPLE      | users_project_issues                    | eq_ref | PRIMARY                                                                                     | PRIMARY   | 4       | drupal6.project_issues.assigned |      1 |                                              | 
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------------------+-----------+---------+---------------------------------+--------+----------------------------------------------+
7 rows in set (0.00 sec)

How's that?

Ok, I'm not positive this is the best approach, but attached patch removes the WHERE node.type in ('project_issue') by relying on the fact that we're INNER JOINing on {project_issues} and {project_projects} etc. It results in the following query:

mysql> EXPLAIN SELECT node.nid AS nid,
 node_project_issues_1.title AS node_project_issues_1_title,
 node_project_issues_1.nid AS node_project_issues_1_nid,
 node_project_issues_1__project_projects.uri AS node_project_issues_1__project_projects_uri,
 node.title AS node_title,
 project_issues.sid AS project_issues_sid,
 project_issues.priority AS project_issues_priority,
 project_issues.category AS project_issues_category,
 node_comment_statistics.comment_count AS node_comment_statistics_comment_count,
 node.type AS node_type,
 node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp,
 users_project_issues.name AS users_project_issues_name,
 users_project_issues.uid AS users_project_issues_uid
 FROM node node
 INNER JOIN project_issues project_issues ON node.nid = project_issues.nid
 INNER JOIN users users_project_issues ON project_issues.assigned = users_project_issues.uid
 LEFT JOIN node node_project_issues ON project_issues.rid = node_project_issues.nid
 INNER JOIN node node_project_issues_1 ON project_issues.pid = node_project_issues_1.nid
 INNER JOIN project_projects node_project_issues_1__project_projects ON node_project_issues_1.nid = node_project_issues_1__project_projects.nid
 LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
 WHERE (node.status <> 0 OR node.uid = 1 or 1 = 1) AND (project_issues.sid in ('1', '8', '13', '14', '15', '2', '4'))
 ORDER BY node_comment_statistics_last_comment_timestamp DESC;
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------+--------------------+---------+-----------------------------------------------------+------+---------------------------------+
| id | select_type | table                                   | type   | possible_keys                                                             | key                | key_len | ref                                                 | rows | Extra                           |
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------+--------------------+---------+-----------------------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | node_project_issues_1__project_projects | ALL    | PRIMARY                                                                   | NULL               | NULL    | NULL                                                | 4349 | Using temporary; Using filesort | 
|  1 | SIMPLE      | project_issues                          | ref    | PRIMARY,project_issues_pid,project_issues_sid,project_issues_nid_assigned | project_issues_pid | 4       | drupal6.node_project_issues_1__project_projects.nid |   47 | Using where                     | 
|  1 | SIMPLE      | node                                    | eq_ref | PRIMARY,nid                                                               | PRIMARY            | 4       | drupal6.project_issues.nid                          |    1 |                                 | 
|  1 | SIMPLE      | node_comment_statistics                 | eq_ref | PRIMARY                                                                   | PRIMARY            | 4       | drupal6.node.nid                                    |    1 |                                 | 
|  1 | SIMPLE      | node_project_issues                     | eq_ref | PRIMARY,nid                                                               | PRIMARY            | 4       | drupal6.project_issues.rid                          |    1 | Using index                     | 
|  1 | SIMPLE      | node_project_issues_1                   | eq_ref | PRIMARY,nid                                                               | PRIMARY            | 4       | drupal6.node_project_issues_1__project_projects.nid |    1 |                                 | 
|  1 | SIMPLE      | users_project_issues                    | eq_ref | PRIMARY                                                                   | PRIMARY            | 4       | drupal6.project_issues.assigned                     |    1 |                                 | 
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------+--------------------+---------+-----------------------------------------------------+------+---------------------------------+
7 rows in set (0.00 sec)

At davidstrauss's suggestion in IRC, we benchmarked the queries from #9 and #10 using SELECT SQL_NO_CACHE ... LIMIT 50 and found the following results:

#9: 50 rows in set (2.95 sec)
#10: 50 rows in set (0.02 sec)

So, #10 seems like a clear winner. ;) Committed those patches to HEAD.

(Note: these results depend on #375342: Unnecessary LEFT JOIN on {node_comment_statistics} causing bad query performance landing in views, too).