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

How many records should it be returning? :p

returning just 2 records

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

commented: Useful question +5

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

commented: Useful +5

execute this

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

first and see how many records will the op?

"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

"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..

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.