Re: Slow query with order by and limit
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.
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
2025
December 17, 2010 05:43AM
1288
December 17, 2010 05:59AM
1153
December 17, 2010 07:58AM
1147
December 17, 2010 08:20AM
1251
December 17, 2010 09:04AM
2332
December 17, 2010 09:29AM
1163
December 17, 2010 09:53AM
1180
December 17, 2010 10:04AM
1608
December 17, 2010 11:47PM
1280
December 18, 2010 12:39AM
1258
December 18, 2010 10:50AM
996
December 20, 2010 04:46AM
1076
December 17, 2010 09:31AM