0

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

Edited by aithabuddy: n/a

4
Contributors
9
Replies
10
Views
6 Years
Discussion Span
Last Post by karthik_ppts
1

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

Votes + Comments
Useful question
0

execute this

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

first and see how many records will the op?

Edited by karthik_ppts: n/a

0

"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

Edited by aithabuddy: n/a

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

0

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

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

Edited by karthik_ppts: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.