I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 minute. And if added more it takes infinite time. Any idea how to resolve this problem?

This is my query:

SELECT  c.`user_name`, e.`event_name`, e.`event_code`, e.`event_id`,
        COUNT(distinct ep.`participant_id`) as participants,
        COUNT(DISTINCT pm.`program_material_id`) as material_count,
        COUNT(DISTINCT ev.`event_news_id`) as news_count ,
        COUNT( DISTINCT  es.`event_speaker_id`) as speaker_count,
        COUNT( DISTINCT  epr.`event_program_id`) as program_count,
        COUNT( DISTINCT  sw.`social_id`) as socail_wall_count
FROM `event` e
LEFT JOIN `event_participant` ep ON ep.`event_id` = e.`event_id`
LEFT JOIN `program_material`  pm ON pm.`event_id` = e. `event_id`
LEFT JOIN `event_news` ev     ON ev.`event_id` = e. `event_id`
LEFT JOIN `socialwall` sw     ON sw.`event_id` = e. `event_id` 
LEFT JOIN `event_speaker`     es ON es.`event_id` = e. `event_id`
LEFT JOIN `event_program`   epr ON epr.`event_id` = e. `event_id`
LEFT JOIN `event_customer`    ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer`          c ON ec.`customer_id` = c.`user_id`   
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

I indexed all tables' primary keys and the columns which I used to JOIN in the subsequent tables. Here event is the master table and all other tables will have event_id.

Giving the system specs if it might helpful,


mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Intel(R) Core(TM) i5-7200U CPU @ 2.50GHz- Thinkpad
  • COUNT each separate table in separate CTE, then join to main table. If main table is huge, LIMIT it in separate CTE, then use шт counting CTEs.
    – Akina
    Oct 24, 2019 at 13:47
  • 1
    Please consider reading about Asking query performance questions
    – mustaccio
    Oct 24, 2019 at 14:14

1 Answer 1


Your query looks like a different approach will be better than simply using more and more LEFT JOINs.

Instead, move to using subqueries in the SELECT clause for most of these "added tables". You shouldn't need the DISTINCT anymore, either, if that was included just to avoid the explosion of rows from so many JOINs.

FROM `event_participant` ep
WHERE ep.event_id = e.event_id
) as participants,
FROM `program_material` pm
WHERE pm.event_id = e.event_id
) as material_count,
FROM `event_news` ev
WHERE ev.event_id = e.event_id
) as news_count ,
FROM `socialwall` sw
WHERE sw.event_id = e.event_id
) as socail_wall_count,
FROM `event_speaker` es
WHERE es.event_id = e.event_id
) as speaker_count,
FROM `event_program` epr
WHERE epr.event_id = e.event_id
)  as program_count
FROM `event` e
LEFT JOIN `event_customer` ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer` c ON ec.`customer_id` = c.`user_id`    
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

Give this a try and see if it is returning the correct results (I had to make some assumptions based on the limited information I have available), and is performing well enough or not.

  • Will try this an let you know
    – KTM
    Oct 24, 2019 at 13:56
  • Ah! A good example of explode-implode.
    – Rick James
    Oct 24, 2019 at 23:49
  • @Willem This works great! and it took only 1.18 sec to fetch the result. Thanks!
    – KTM
    Oct 25, 2019 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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