Results 1 to 8 of 8
Thread: TABLE SCAN and LIMIT
May 3, 2008, 01:25 #1
- Join Date
- Mar 2005
- 0 Post(s)
- 0 Thread(s)
TABLE SCAN and LIMIT
I have a table with the following structure
user_id (int PRIMARY AUTO INCREMENT)
status (tinyint) - possible values 1, 2 and 3
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.
Any suggestions on how to optimize this particular table?
Last edited by viv5; May 3, 2008 at 05:14.