SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Germany
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Short question about index usage

    I have an index on (col1, col2). The following query uses this index and EXPLAIN SELECT says "Using Where", great!
    Code:
    SELECT * FROM table WHERE col1=2 ORDER BY col2 DESC
    But the follwing slightly modified query says "Using where; Using filesort":
    Code:
    SELECT * FROM table WHERE col1 IN (2,3) ORDER BY col2 DESC
    Why doesn´t it use the index to sort the rows in this case?

    I´m using MySQL 4.0.23, might this be a bug?

  2. #2
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Germany
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I now know why this does not work.

    The index ist primarily sorted by col1 and secondarily by col2. If MySQL now looks at the index rows where col1=1 the values of col2 are already correctly sorted. But if MySQL has to look at rows where col1 IN (1,2,...) the col2-column of the matching index rows is not already sorted by col2. The col2-column of the index is only correctly sorted within one value of col1.


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
  •