Very, very slow query

Question

I have this query:

SELECT 
    a.id,
    a.name, 
    count(b.id),
    count(c.id), 
    count(e.id), 
    count(f.id)
FROM 
    organizations a
LEFT JOIN vessels b ON a.id = b.organization_id
LEFT JOIN licences c ON a.id = c.organization_id
LEFT JOIN fleets e ON a.id = e.organization_id
LEFT JOIN users f ON a.id = f.organization_id
GROUP BY a.id;

In all tables there's a proper index (on the primary index, and organization_id), there's about 80 rows in organizations, 400 in fleets, 2900 in vessels, 3000 in licences and 10 in users

This query doesn't even succeed, it's stuck on copying to temp table

How should I re-work this query to make it work (fast) ?

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  a   index       PRIMARY 4       1   
1   SIMPLE  b   ref organisation_id organisation_id 4   fuel.a.id   70  Using index
1   SIMPLE  c   ref organisation_id organisation_id 4   fuel.a.id   15  Using index
1   SIMPLE  e   ref organisation_id organisation_id 4   fuel.a.id   5   
1   SIMPLE  f   ref organization_id organization_id 5   fuel.a.id   1   Using index

Answer 1

Your joins do not depend on each other, that's why the temp tables are exploding.

A simple fix is to make:

SELECT 
    a.id,
    a.name, 
    (select count(*) from vessels b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from licenses b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from fleets b where a.id = b.organization_id group by b.organization_id),
    (select count(*) from users b where a.id = b.organization_id group by b.organization_id),
FROM 
    organizations a

It will be far more faster, if you do it like this:

SELECT 
    a.id,
    a.name,
    v.total,
    w.total,        
    x.total,
    y.total
FROM 
    organizations a
LEFT JOIN (select b.organizantion_id, count(*) total from vessels b group by b.organization_id) v on v.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from licenses b group by b.organization_id) w on w.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from fleets b group by b.organization_id) x on x.organization_id=a.id
LEFT JOIN (select b.organizantion_id, count(*) total from users b group by b.organization_id) y on y.organization_id=a.id