MySQL JOIN query takes too much time to get the result

join;MySQLoptimizationperformancequery-performance

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,

SPECS

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

Best Answer

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.

SELECT
c.`user_name`,
e.`event_name`,
e.`event_code`,
e.`event_id`,
(
SELECT COUNT(*)
FROM `event_participant` ep
WHERE ep.event_id = e.event_id
) as participants,
(
SELECT COUNT(*)
FROM `program_material` pm
WHERE pm.event_id = e.event_id
) as material_count,
(
SELECT COUNT(*)
FROM `event_news` ev
WHERE ev.event_id = e.event_id
) as news_count ,
(
SELECT COUNT(*)
FROM `socialwall` sw
WHERE sw.event_id = e.event_id
) as socail_wall_count,
(
SELECT COUNT(*)
FROM `event_speaker` es
WHERE es.event_id = e.event_id
) as speaker_count,
(
SELECT 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.

Related Question