MySQL Forums
Forum List  »  Performance

Re: Slow query with order by and limit
Posted by: Thomas Wiedmann
Date: December 17, 2010 05:59AM

FORCE_INDEX is not the right way to optimize the query only a compromise.
Did you add my index suggestion on table NODE and TERM_NODE?

I try to optimize this query from your first message:
EXPLAIN 
SELECT node.nid AS nid, node.created AS node_created, node.title AS node_title
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
WHERE
node.type IN ('news')
AND node.STATUS <>0
AND term_node.tid =10
ORDER BY node_created DESC
LIMIT 0 , 3

About your question:
2) Index is faster than filesort

First please try my Index hints.

ALTER TABLE term_node 
 DROP KEY vid, 
 DROP KEY tid, 
 ADD KEY idx_vid_tid (vid,tid), 
 ADD KEY idx_tid_vid (tid,vid); 

ALTER TABLE node 
DROP KEY node_type, 
ADD KEY idx_type_status_vid (type(4), status, vid);

with kind regards,
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
4964
December 16, 2010 07:43AM
Re: Slow query with order by and limit
1287
December 17, 2010 05:59AM
1251
December 17, 2010 09:04AM
1163
December 17, 2010 09:53AM
1608
December 17, 2010 11:47PM
1257
December 18, 2010 10:50AM


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.