我有一個 MySQL 查詢如下，它有JOIN8 個表。當我使用 3 個表來獲取數據時，結果會在 10 秒內得到。但是，每當我再添加一張桌子時，提取時間就會長達 1 分鐘。如果添加更多，則需要無限時間。知道如何解決這個問題嗎？

這是我的查詢：

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

我索引了所有表的主鍵和我JOIN在後續表中使用的列。這event是主表，所有其他表都有event_id。

如果可能有幫助，請提供系統規格，

眼鏡

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

您的查詢看起來像一種不同的方法會比簡單地使用越來越多的 LEFT JOIN 更好。

相反，對於大多數這些“添加的表”，請在 SELECT 子句中使用子查詢。你也不應該再需要 DISTINCT，如果它只是為了避免來自這麼多 JOIN 的行爆炸。

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

試一試，看看它是否返回了正確的結果（我不得不根據我現有的有限資訊做出一些假設），並且性能是否足夠好。

引用自：https://dba.stackexchange.com/questions/251873