I have this query

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 
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 

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

  • Don't know how.... – harmstra Jun 19 '12 at 12:52
  • It would be preferable if you posted the EXPLAIN output with \G at the end instead of ; for a vertical layout, which is much more readable. – Aaron Brown Jun 19 '12 at 13:39
  • I'm very curious why do you need numbering your output columns? It doesn't help too much with readability... – dezso Jun 19 '12 at 13:46
  • 1
    I see a GROUP BY, but no aggregate functions. So I wonder what the purpose of the GROUP BY is. Does it have any effect on the result at all? (I am not a MySQL specialist) – Olivier Jacot-Descombes Jun 19 '12 at 13:54
  • Hi Harmstra, welcome to the site! What version of MySQL? What are the hardware specs of the server (RAM, Disk setup, etc)? Can you show the DDL of at least the members and requests tables? – Derek Downey Jun 19 '12 at 14:01

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.

| improve this answer | |
  • I could post the table structure here but that's a lot of info cause some tables have lotst of fields – harmstra Jun 19 '12 at 14:07
  • 1
    @Harmstra - there is SQLFiddle, a very convenient online tool for posting lots of SQL code. – dezso Jun 19 '12 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.