Over and over again mysql slow quries log this query taking a long time.
SELECT DISTINCT COUNT(DISTINCT(n.nid)) FROM main_node n LEFT JOIN main_comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS
NULL) INNER JOIN main_node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid
= 2 AND na.realm = 'workflow_access') OR (na.gid = 3128 AND na.realm = 'workflow_access_owner') OR (na.gid = 0 AND na.realm = 'og_pu
blic') OR (na.gid = 2205 AND na.realm = 'og_subscriber'))) AND ( n.status = 1 AND (n.uid = 3128 OR c.uid = 3128));
When I tried Explain it shows
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE n ALL PRIMARY,uid,node_status_type,nid NULL NULL NULL 10452 Using where
1 SIMPLE na ref PRIMARY PRIMARY 4 drp1.n.nid 3 Using where
1 SIMPLE c ref status,nid nid 4 drp1.n.nid 19 Using where For some reason it does not use any index for first part. I tried removing "LEFT JOIN main_comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL) " And then it uses index fine. But that will change the purpose of qury which si trying to join only published comments?
This is basic query when OG module is installed and shows the tracker which searches on OG posts.
How can I modify this query? Any other thoughts? Is there any column I should add index to speed this up? It is currently going through 10542 records and returning only 1.
Comments
Experiment
I've fixed some outstanding bugs with my dbtuner module; mind giving it a test? Query looks like its from a view so it should pick it up.
For anyone looking here's the query in an easier to read form
SELECT DISTINCT COUNT(DISTINCT(n.nid))FROM node n
LEFT JOIN comments c ON n.nid = c.nid AND (c.status = 0 OR c.status IS NULL)
INNER JOIN node_access na ON na.nid = n.nid
WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all')
OR (na.gid = 2 AND na.realm = 'workflow_access')
OR (na.gid = 3128 AND na.realm = 'workflow_access_owner')
OR (na.gid = 0 AND na.realm = 'og_public')
OR (na.gid = 2205 AND na.realm = 'og_subscriber')))
AND ( n.status = 1 AND (n.uid = 3128 OR c.uid = 3128));
The query turned out to be
Thanks for cleaning the query.
The query turned out to be coming from core tracker module (when OG access is installed). There is a long discussion on improving the query. This was finally fixed in D7 but not aware if there is D6 port available.
http://drupal.org/node/105639
Tracker Module
http://drupal.org/project/tracker2