MYSQL Slow SELECT with multiple joins and ORDER BY

Question

I have this query

SELECT 
r0_.id AS id0,
r0_.deleted_at AS deleted_at1,
r0_.updated_at AS updated_at2,
r0_.created_at AS created_at3,
r0_.is_vehicle_offer AS is_vehicle_offer4,
r0_.vehicle_offer_price AS vehicle_offer_price5,
r0_.vehicle_offer_description AS vehicle_offer_description6,
r0_.nr_of_parts_found_mails_sent AS nr_of_parts_found_mails_sent7,
r0_.client_id AS client_id8,
r0_.parent_id AS parent_id9,
r0_.vehicle_id AS vehicle_id10 
FROM 
requests r0_ 
INNER JOIN vehicles v1_ ON r0_.vehicle_id = v1_.id 
INNER JOIN sibren_brands s2_ ON v1_.sibren_brand_id = s2_.id 
INNER JOIN member_sibben_brands_subscriptions m4_ ON s2_.id =m4_.sibren_brand_id 
INNER JOIN members m3_ ON m3_.id = m4_.member_id 
INNER JOIN member_vehicle_categories m6_ ON m3_.id = m6_.member_id 
INNER JOIN vehicle_categories v5_ ON v5_.id = m6_.vehicle_category_id 
INNER JOIN member_sibren_brands_filter m8_ ON s2_.id = m8_.sibren_brand_id 
INNER JOIN members m7_ ON m7_.id = m8_.member_id 
LEFT JOIN request_metadata r9_ ON r0_.id = r9_.request_id AND (r9_.member_id = '210')
WHERE m3_.id = '210' AND v5_.id = s2_.vehicle_category_id AND
m7_.id = '210' AND r0_.updated_at >= '2012-04-19 13:31:26' AND
(r9_.id IS NULL OR r9_.deleted_at IS NULL) AND
r0_.updated_at <= '2012-06-19 13:31:25' 
GROUP BY r0_.id 
ORDER BY r0_.updated_at DESC 
LIMIT 25 OFFSET 0;

Wich takes about 1.5 secs to run. If I remove the last part GROUP BY .etc..... than it only takes 0.07 sec.

Here's the result of EXPLAIN

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  m7_ const   PRIMARY PRIMARY 4   const   1   Using index
1   SIMPLE  m6_ ref PRIMARY,vehicle_category_id,member_id   PRIMARY 4   const   1   Using where; Using index
1   SIMPLE  v5_ eq_ref  PRIMARY PRIMARY 4   oz_nl.m6_.vehicle_category_id   1   Using index
1   SIMPLE  s2_ ref PRIMARY,vehicle_category_id vehicle_category_id 4   oz_nl.m6_.vehicle_category_id   37  Using index
1   SIMPLE  m4_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  m8_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  v1_ ref PRIMARY,sibren_brand_id sibren_brand_id 5   oz_nl.s2_.id    111 Using where; Using index
1   SIMPLE  r0_ ref vehicle_id,updated_at   vehicle_id  4   oz_nl.v1_.id    1   Using where
1   SIMPLE  r9_ eq_ref  request_id,member_id,request_id_2   request_id  8   oz_nl.r0_.id,const  1   Using where

As far as i can see indexes are set OK

Answer 1

The issue that jumps out at me is the first table in your EXPLAIN:

1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort 

As you've noticed, if you remove the GROUP BY...ORDER BY..., the query runs much faster. Doing so most likely removes the Using temporary;Using filesort (but I can't know for sure without the CREATE TABLE statements.

I would suspect this is creating the temporary table on disk. See this link on how MySQL uses temporary tables.

First, if you are selecting any BLOB or TEXT columns, this will automatically be an on-disk temporary table. From your list, it looks like r0_.vehicle_offer_description AS vehicle_offer_description6 could fit the bill.

If this is the case, try removing that column and see if there is any noticeable difference.