Query Optimization

[ifty2k]

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?

[kickstart]

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

[fenway]

Easy -- you don't have an index on (field1,field2).

[ifty2k]

Thanks so much - worked perfectly!