ifty2k Posted August 14, 2010 Share Posted August 14, 2010 Hi, I have 1 table with more than a million entries. I am trying to run the following queries: SELECT id FROM `table` WHERE field1 =1 ORDER BY `field2` DESC LIMIT 0 , 30 ****Query took 7.4701 sec SELECT id FROM `table` WHERE field1 =1 LIMIT 0 , 30 ****Query took 0.0006 sec SELECT id FROM `table` ORDER BY `field2` DESC LIMIT 0 , 30 ****Query took 0.0004 sec Both field1 and field2 are indexed. Why is the first query so slow and how do I speed it up? Quote Link to comment https://forums.phpfreaks.com/topic/210721-query-optimization/ Share on other sites More sharing options...
kickstart Posted August 14, 2010 Share Posted August 14, 2010 Hi Can you do explains on the 3 queries? It could be that the values in the indexes are not unique enough and so are being ignored. A the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/210721-query-optimization/#findComment-1099282 Share on other sites More sharing options...
fenway Posted August 14, 2010 Share Posted August 14, 2010 Easy -- you don't have an index on (field1,field2). Quote Link to comment https://forums.phpfreaks.com/topic/210721-query-optimization/#findComment-1099297 Share on other sites More sharing options...
ifty2k Posted August 16, 2010 Author Share Posted August 16, 2010 Thanks so much - worked perfectly! Quote Link to comment https://forums.phpfreaks.com/topic/210721-query-optimization/#findComment-1099754 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.