mysql query having orderby, limt

[aithabuddy]

Hi

I have a problem with the mysql query that uses both order by and limit.Following is the sql query i'm using,

select * from temp where venue <> '' and cat1 = 'sports' and id < 74528 order by id desc limit 10;

in this temp table 'id' is primary key, and i created index on venue, cat1. This query is examining 78000 records out of 90000. If i can remove order by or limit then it's examining 2000 records only. I need both order by id, limit in this query but number records examining should reduce.

Can you please tell me the reason why it's performing like that?

Thank You

[GigsD4X]

How many records should it be returning? :p

[aithabuddy]

returning just 2 records

[vibhaJ]

BTW its different question but how to see that query has examined how many record?
I am having mysql query browser and phpmyadmin.

[aithabuddy]

'Explain SQL' option presents, click on that, it will show you the number of records examined

[karthik_ppts]

execute this

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528"

first and see how many records will the op?

[aithabuddy]

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528"

for this query 2129 records,

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528 order by id desc"

for this query 2129 records,

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528 limit 10"

for this query 2129 records,

but if i use order by. limit together

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528 order by id desc limit 10"
it's examining 90000 records

[karthik_ppts]

"select * from temp where venue <> '' and cat1 = 'sports' and id < 74528 limit 10"

Are you sure this query returns 2129 records?

it should return only 10 records..

[aithabuddy]

It's returning 10 records only, but examining 2190 records

[karthik_ppts]

It's returning 10 records only, but examining 2190 records

oh.. ok... i misunderstood your question :S