Hardcore MySQL query optimisation

Mort · 21 Jan 2011 at 09:41

Hey all

Having some issues optimising a query in mysql, and wondered if any of the gurus in here could pick it apart?

I know about adding indexes and stuff but optimising really high result set queries is doing me in a bit

All info is here - http://stackoverflow.com/questions/...ql-structure-queries-for-large-mailing-groups

Any help would be appreciated

Mort · 28 Jan 2011 at 13:22

ahh nice one

got a few bits to do today but I will have a look as soon as I get a chance, thanks for the responses!

Mort · 1 Feb 2011 at 10:31

Hambut_Bulge said:
Not sure if you're still needing help with this, but here goes...

Dude

Nice one, trying to puzzle this out at the mo, its returning 0 at the mo but I am sure thats because of my original explanation rather than your solution, thanks muchly!

Mort · 1 Feb 2011 at 10:43

Ahh, the inner join needed to be a.site_id = b.site_id

Thanks again Hambut_Bulge, you are an SQL machine

EXPLAIN BEFORE

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY mgroups ALL PRIMARY,mg_id NULL NULL NULL 9 Using temporary; Using filesort
1 PRIMARY mgroups_explicit ref me_mg_id me_mg_id 4 engine_4.mgroups.mg_id 8750
1 PRIMARY contacts ALL PRIMARY,c_id NULL NULL NULL 86012 Using where; Using join buffer
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const table..

EXPLAIN AFTER

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE d system NULL NULL NULL NULL 0 const row not found
1 SIMPLE b const PRIMARY,mg_id PRIMARY 4 const 1
1 SIMPLE c system me_mg_id NULL NULL NULL 1
1 SIMPLE a ref site_id site_id 4 const 8155 Using where

Win