SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Jan 2007
    Posts
    323
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    indexes for this query?

    What indexes would I add to make this table not use filesort? I've tried all indexes I can think of and it always resorts to filesort.

    Code:
    SELECT CONCAT( REPEAT( ' ', COUNT( parent.title ) -1 ) , node.title ) AS name, GROUP_CONCAT( parent.title
    ORDER BY parent.left_
    SEPARATOR '~' ) AS 'path', node.left_
    FROM category AS node
    LEFT JOIN category AS parent ON node.left_
    BETWEEN parent.left_
    AND parent.right_
    GROUP BY node.title
    ORDER BY path, node.title;
    Explain gives:
    Using temporary; Using filesort

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please post the CREATE TABLE statements for category and parent, plus the output of EXPLAIN.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •