3

I have mysql db with 7 Million records

when I run query like

select  * from  data where cat_id=12 order by id desc limit 0,30

the query take long time like 0.4603 sec

but same query with out (where cat_id=12) or with out (order by id desc) very Fast the query take long time like 0.0002 sec

I have indexes on cat_id and id

there is any way to make query with (where and order by) fast

thanks

John Conde
  • 217,595
  • 99
  • 455
  • 496
khalid seleem
  • 117
  • 1
  • 8

2 Answers2

4

Create a composite index that combines cat_id and id. See http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html for syntax and examples.

If you state 'cat_id=12' only, you will get all matching rows, which is fast, because of the index. But these rows won't be ordererd, so mysql has to read them all into a temporary table and sort that table, which is slow.

Similarly, 'order by id desc' will order the rows quickly, but mysql has to read all of them to find out which have 'cat_id=12', which is slow.

A composite index should solve these issues.

Guntram Blohm
  • 9,667
  • 2
  • 24
  • 31
0

It is running fast without order by since when you write order by DESC then it first iterates through all the rows and then it selects in descending order. Removing the condition makes it by default ASCENDING which makes it fast.

Also it may be that your index is sorted ascending so when you ask for descending it needs to do a lot more work to bring it back in that order

Marco
  • 56,740
  • 14
  • 129
  • 152
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331