title: Optimizing ORDER BY Query on large table MySQL
body: <p>I have a browse category query that im trying to optimize. Im ending up with Using temporary; Using filesort in the explain and the query is slow on a category with say 60,000 rows. If i remove the Order By clauses the query runs very fast .05 seconds to do 60,000 rows. With the Order By clauses its very slow around 5 seconds. Parts contains some 500,000 rows as does Parts_Category.</p>
  
  <p>I have a group index on Parts (status, level, warehouse, updated) called sort_index</p>
  
  <p>At the top of the explain I have  | ALL | Using temporary; Using filesort</p>
  
  <p>All the other indexes are showing OK. Can somebody please tell me what the problem might be? Im out of ideas. Maybe i should rearrange this query so i can get better performance perhaps?</p>
  
  <p>query. </p>
  
  <pre><code>SELECT Parts.*, Image.type, Image.width, Image.height,
  (SELECT name FROM Location_State WHERE id = Parts.state_id) AS state, 
  (SELECT name FROM Location_Region WHERE id = Parts.region_id) AS region, 
  (SELECT start_date FROM Promotion WHERE id = Parts.promotion_id) AS promotion_start_date, 
  (SELECT end_date FROM Promotion WHERE id = Parts.promotion_id) AS promotion_end_date 
  FROM ( SELECT parts_id FROM Parts_Category WHERE Parts_Category.category_id = '40' 
  UNION SELECT parts_id FROM Parts_Category WHERE Parts_Category.main_category_id = '40') cid 
  LEFT JOIN Image ON Parts.image_id = Image.id
  JOIN Parts ON Parts.id = cid.parts_id AND Parts.status = 'A'
  ORDER BY Parts.level DESC, Parts.warehouse DESC, Parts.updated DESC LIMIT 0, 15
  </code></pre>
  
  <pre>
  Table structure for table Parts
  
  Field   Type    Null    Default
  id  int(11) No  auto_increment
  image_id    int(11) Yes 0
  gallery_id  int(11) Yes 0
  image_count int(3)  Yes 0
  promotion_id    int(11) Yes 0
  country_id  int(11) Yes NULL
  state_id    int(11) Yes NULL
  region_id   int(11) Yes NULL
  city_id int(11) Yes NULL
  area_id int(11) Yes NULL
  updated datetime    Yes 0000-00-00 00:00:00
  entered datetime    Yes 0000-00-00 00:00:00
  renewal_date    date    Yes 0000-00-00
  discount_id varchar(10) Yes NULL
  title           varchar(100)    Yes 
  search_title    varchar(255)    Yes 
  warehouse   varchar(50) Yes 
  url varchar(255)    Yes 
  display_url varchar(255)    Yes 
  friendly_url    varchar(100)    Yes NULL
  description varchar(255)    Yes 
  keywords    varchar(1000)   Yes NULL
  attachment_file varchar(255)    Yes 
  attachment_caption  varchar(255)    Yes 
  status  char(1) Yes 
  level   tinyint(3)  Yes 0
  worldwide   tinyint(1)  Yes 0
  random_number   int(11) Yes NULL
  reminder    tinyint(4)  Yes NULL
  category_search varchar(1000)   Yes 
  video_snippet   varchar(1000)   Yes 
  importID    int(11) Yes 0
  
  Indexes
  
  PRIMARY             518623       id
  random_number INDEX 32201   random_number
  country_id  INDEX   1       country_id
  state_id    INDEX   8       state_id
  region_id   INDEX   5       region_id
  renewal_date    INDEX   1       renewal_date
  worldwide   INDEX   1       worldwide
  friendly_url    INDEX   518623      friendly_url
  promotion_id    INDEX   1       promotion_id
  city_id         INDEX   1   city_id
  area_id     INDEX   1       area_id
  zip_code    INDEX   2790        zip_code
  importID    INDEX   518623      importID
  image_id    INDEX   10          image_id
  
  --------------
  index_browse_category   INDEX   52 
  level
  status
  warehouse   
  updated
  -----------------
  keywords    FULLTEXT    1 
  description
  keywords
  category_search 
  
  
  Parts_Category
  
  id              int(11)         No   auto_increment     
  parts_id        int(11)             No  0       
  category_id         int(11)             No  0       
  main_category_id    int(10)             No  0   
  
  Index
  
  PRIMARY          PRIMARY    519330           id
  category_id          INDEX  519330          category_id
  parts_id
  main_category_id     INDEX  519330              main_category_id
  parts_id
  
  
  
  
  
  </pre>