SELECT DISTINCT(n.nid) FROM node n INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid LEFT JOIN project_comments c ON c.nid = p.nid WHERE n.status = 1

This query does not use an index on the project_issues table.

After doing
alter table project_issues add index (nid,assigned);
the new index is used.

CommentFileSizeAuthor
#5 pi.nid_assigned.patch_2.txt1.91 KBdww
#3 pi.nid_assigned.patch1.84 KBhunmonk

Comments

gerhard killesreiter’s picture

gerhard killesreiter commented

Not sure what has been changed inthe meantime but the new index is not used.

gerhard killesreiter’s picture

gerhard killesreiter commented

I am confused

alter table project_issues add index nid_assigned (nid,assigned);

The explain says:

|  1 | SIMPLE      | p     | index  | PRIMARY,nid_assigned                  | nid_assigned         | 8       | NULL               | 68681 | Using index; Using temporary |
|  1 | SIMPLE      | n     | ref    | PRIMARY,vid,node_status_type,nid,tracker_global,node_status_type_uid | PRIMARY              | 4       | scratch.p.nid      |     1 | Using where                  |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                  | PRIMARY              | 4       | scratch.p.assigned |     1 | Using index; Distinct        |
|  1 | SIMPLE      | r     | eq_ref | PRIMARY                  | PRIMARY              | 4       | scratch.n.vid      |     1 | Using index; Distinct        |
|  1 | SIMPLE      | c     | ref    | project_comments_nid                  | project_comments_nid | 4       | scratch.p.nid      |     2 | Using index; Distinct        |
hunmonk’s picture

hunmonk commented
StatusFileSize
new pi.nid_assigned.patch1.84 KB

attached patch adds the described index. untested.

hunmonk’s picture

hunmonk commented
Status: Active » Needs review

works for updates and fresh install on postgres.

dww’s picture

dww
we/he/they
commented
Status: Needs review » Reviewed & tested by the community
StatusFileSize
new pi.nid_assigned.patch_2.txt1.91 KB

typos causing SQL errors in the update on mysql. new patch works fine on mysql upgrade and install. since i didn't touch the pgsql cases, I'm calling this RTBC. ;)

hunmonk’s picture

hunmonk commented
Status: Reviewed & tested by the community » Fixed

comitted to HEAD, 4.7.x-1.x, 4.7.x-2.x

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)