MYSQL Slow SELECT with multiple joins and ORDER BY

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_c

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.

StackExchange Database Administrators Q#19493, answer score: 2