mysql - 如何优化我的MySQL查询？因为需要45秒才能获取记录

我的数据库表“ ringtune_history”具有40K记录，“ ringtunes”具有4K记录，查询处理时间过多。

SELECT  `ringtunes`.*,
        count(case when ringtune_history.Action = 'Download' then ringtune_history.Action end) as Org_Downloads,
        count(case when ringtune_history.Action = 'View' then ringtune_history.Action end) as Org_Views,
        count(case when ringtune_history.Action = 'Play' then ringtune_history.Action end) as Total_Plays,
        count(case when ringtune_history.Action = 'Like' then ringtune_history.Action end) as Total_Likes,
        `categories`.`Name` as `Category_Name`
    FROM  `ringtunes`
    LEFT JOIN  `ringtune_history`  ON `ringtune_history`.`Ringtune_Id` = `ringtunes`.`Id`
    LEFT JOIN  `categories`  ON `categories`.`Id` = `ringtunes`.`Category`
    WHERE  `ringtunes`.`Deleted` = 0
      AND  `ringtunes`.`Status` = 1
      AND  `categories`.`Deleted` = 0
      AND  `categories`.`Status` = 1
    GROUP BY  `ringtunes`.`Id`
    ORDER BY  `ringtunes`.`Id` DESC
    LIMIT  20

最佳答案

您的查询很好！问题在于表本身。

确保将索引添加到用于查找记录的列中（例如，ringtunes.Deleted = 0，索引将需要进入Deleted列）。您检查值的其他列也是如此。

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html