MySQL Forums
Forum List  »  Performance

Re: Optimize MySQL queries
Posted by: Rick James
Date: July 08, 2009 09:52AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4581
July 08, 2009 12:04AM
Re: Optimize MySQL queries
2075
July 08, 2009 09:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.