MySQL Forums
Forum List  »  Performance

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

1.
It is harmful to put an index on most of the column like you don on table NODE.

2.
Think about this UNIQUE KEYs (really usefull?)
- UNIQUE KEY `vid` (`vid`),
- UNIQUE KEY `vid_created` (`vid`,`created`),


3.
Try to combine your Index on Table TERM_NODE

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

ANALYZE TABLE term_node;

4.
Your table node_term only has INT columns. Maybe CHARSET=UTF8 is not necessary. Check you can move to LATIN1 (Smaller data footprint).

5.
Try to combine your Index on Table NODE

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

ANALYZE TABLE node;

Please EXPLAIN your Query again.

with kind regards
Thomas



Edited 4 time(s). Last edit at 12/17/2010 01:20AM by Thomas Wiedmann.

Options: ReplyQuote


Subject
Views
Written By
Posted
4964
December 16, 2010 07:43AM
Re: Slow query with order by and limit
1463
December 17, 2010 12:55AM
1251
December 17, 2010 09:04AM
1163
December 17, 2010 09:53AM
1608
December 17, 2010 11:47PM
1258
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.