The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

TABLE SCAN and LIMIT

INDEXES are PRIMARY(user_id) and status(status) and table has nearly 100000 records.

Now most of the queries that we run fetches user records on the basis of their status (1, 2 or 3)

E.g. SELECT * FROM users WHERE status='2' ORDER BY user_id LIMIT 10

Now records associated with every status (1, 2 or 3) are nearly one third of 100000.

Now since we have index on status and user_id, when I run this query with EXPLAIN, it shows me the following data

Now since I have mentioned LIMIT 10 with ORDER BY user_id, why is it scanning all the records with status='2'? When ORDER BY column i.e. user_id (PRIMARY) is already indexed, it should have sorted the first 10 rows and returned the data.

At the moment the SELECTs have become slow and I am afraid that it will get worse with more records.

So what should I do with INDEXES so that it only scans 10 rows or whatever I mention with LIMIT.

If I use USE INDEX (PRIMARY) in the query, it removes "Using filesort" however the number of rows that getting scanned increases to 100000.

I did try the same query after putting an index on (status, user_id) and by dropping an index on status alone however the results are just the same.

It's scanning 32222 rows in order to display just 10.

Does it work like this only? I mean soon the number of records in my database would become 5 times of what they are now, so it means this query would be scanning 5 * 32222 records at that time. This would make the process very very slow.

Originally Posted by dadane

You want it to sort all of your users, -and give you then 10 first right?

If I remove the ORDER BY statement from the query, still the table is scanning all the records (32222) with status=’2’ in order to display just 10.

Thank you guys for your comments. I think longneck is correct as EXPLAIN statement may be giving wrong information for rows scanned in this case.

This is what they've mentioned in their article:One more note about ORDER BY ... LIMIT is - it provides scary explain statements and may end up in slow query log as query which does not use indexes, even if it is quite fast: