0

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
  • 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 at 13:47
  • 1
    Please consider reading about Asking query performance questions – mustaccio Oct 24 at 14:14
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.

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.

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

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.