SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Issue with Uses Filesort

    I'm having trouble getting rid of Using filesort on a query (due to ORDER BY). Here's the query and indexes I've tried:

    Code SQL:
    ## SQL
    SELECT `sticky`, `locked`, `id`, `title`, `views`, `posts`
    FROM `comments` 
    WHERE `type` = 'forum' 
    AND `item_id` = '34' 
    AND `parent` = '0'
    AND `active` = 'Y'
    ORDER BY `sticky`,`updated` DESC
     
    ## Indexes I've tried
    CREATE INDEX type_item_id_parent_active_sticky ON comments (type, item_id, parent, active, sticky);
    CREATE INDEX type_item_id_parent_active_sticky_updated ON comments (type, item_id, parent, active, sticky, updated);

    If I do the ORDER BY with just "sticky" or just "updated" it works as it should.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What about an index on only (sticky, updated)? Why do your indexes have almost every column in them? If the index covers nearly all rows of the table because you've put that many columns into it, it's not really saving you anything even if it's used. The index will be slow as reading the table itself, and the query optimizer knows when it's faster to just go to the table than use an inefficient index and look up every row by pointer.

    Off Topic:

    Why are there quotes around numeric values?

  3. #3
    Formerly known as RockNRollPig Shpigford's Avatar
    Join Date
    Dec 2002
    Location
    Denver, CO
    Posts
    2,877
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried indexing on just sticky/updated and still get using filesort. Creating an index on all those columns solves the Using Filesort issue when then Order By is there. If there's no Order By, then it won't do Using Filesort...but doing that big index fixes it.

    As for quotes on numeric values...just habit.


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
  •