SELECT n.nid,
v.value AS votingapi_cache_vote_percent_average_value
FROM node n
LEFT JOIN term_node t
ON n.nid = t.nid
AND t.tid IN ('400566')
LEFT JOIN votingapi_cache v
ON n.nid = v.content_id
AND v.content_type = 'node'
AND v.value_type = 'percent'
AND v.tag = 'vote'
AND v.function = 'average'
WHERE (n.type IN ('image','video', 'acidfree'))
AND (n.status = '1')
AND (t.tid IS NULL)
ORDER BY votingapi_cache_vote_percent_average_value DESC
This combo seems strange:
AND t.tid IN ('400566')
AND (t.tid IS NULL)
Hmmm, looks like the optimizer failed to test n.nid=400566 first. Instead it went for n.type, which had about 46K rows.
Suggest:
...
FROM node n
AND n.nid IN = 400566
LEFT JOIN term_node t
ON n.nid = t.nid
...
Clean up indexes:
PRIMARY KEY (nid,vid),
UNIQUE KEY vid (vid),
KEY node_type (type(4)), -- prefixes are rarely useful; suggest tossing
KEY node_title_type (title,type(4)), -- rethink
KEY status (status), -- poor cardinality, but was used
KEY uid (uid),
KEY node_moderate (moderate),
KEY node_promote_status (promote,status),
KEY node_created (created),
KEY node_changed (changed),
KEY node_status_type (status,type,nid), -- rearrange:
INDEX (nid, status, type) -- should help after changing SELECT (above)
KEY nid (nid) -- the PK handles this, so toss.
---
PRIMARY KEY (tid,nid),
KEY nid (nid), -- change to (nid,tid)
KEY tid (tid) -- toss
Consider using TINYINT or other size numbers: less space -> more cacheable -> faster.
Consider using ENUM for 'type'.
Please use [ code ] and [ / code ] around any code, SQL or output.
If these suggestions don't make it fast enough; come back for another round of suggestions.