SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Threaded View

  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Location
    India
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    TABLE SCAN and LIMIT

    I have a table with the following structure

    user_id (int PRIMARY AUTO INCREMENT)
    username (varchar)
    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?


    Thanks.
    Last edited by viv5; May 3, 2008 at 06:14.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •