php - 如何使具有许多左连接的 mysql 查询执行得更快？

Question

我已经编写了一个查询，并且在本地运行良好。当它上线时，我收到此错误:

ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

我添加了SET SQL_BIG_SELECTS=1;。它现在可以工作，但运行速度非常慢。任何人都可以整理我的查询，ege 而不是 LEFT JOIN 使用另一种方法。这是我的查询:

    SELECT sh.*,
       ps.provider_name,
       ps.photo_path AS photo
FROM   scholarship sh
       left join cat_category_sch ccs
              ON ccs.scholar_id = sh.id
       left join elg_countries_sch elg
              ON elg.scholar_id = sh.id
       left join study_level_sch sls
              ON sls.scholar_id = sh.id
       left join taken_country_sch tcs
              ON tcs.scholar_id = sh.id
       left join providers ps
              ON ps.id = sh.provider
       left join countires c1
              ON c1.id = elg.county_id
       left join countires c2
              ON c1.id = tcs.county_id
       left join study_level sl
              ON sl.id = sls.study_level_id
WHERE  sh.status = 1
       AND sh.ain_status = 1
       AND sh.deadline >= Date_sub(Now(), interval 1 year)
GROUP  BY sh.id
ORDER  BY sh.id DESC  

提前致谢

Answer 1

LEFT 意味着“右”表可能没有“匹配”行。然而，您不会使用 IS NULL 或 IS NOT NULL 检查是否存在。所以，我怀疑 LEFT 是不必要的？ (我提出这个问题是因为(1)它可能会分散对真正问题的注意力，(2)它可能会减慢查询速度。)

如果需要 LEFT，则 ON tcs.scholar_id = sh.id 需要 scholar_id 上的索引。 (等等)

复合索引 INDEX(status, ain_status, Deadline) 也可能有所帮助。