使用多个selectas列优化查询

khbbv19g 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(319)

我提出了一个问题：

SELECT DISTINCT player_1 AS player, 
                (SELECT COUNT(*) FROM results WHERE player_1=player OR player_2=player) AS since_start_matches, 
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=0) AS since_start_ht_0,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=1) AS since_start_ht_1,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=2) AS since_start_ht_2,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=3) AS since_start_ht_3,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=4) AS since_start_ht_4,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals>=5) AS since_start_ht_5_plus
FROM results ORDER BY player

这个 results 表有25000个条目，执行此查询大约需要7秒，这太长了。这个查询效率非常低，因为我创建的每一列都在同一个表上用不同的条件再次搜索。
我试着为where子句中感兴趣的列建立索引。几秒钟就搞定了。但还是太慢了。
处理此类查询的最佳方法是什么？
我用的是mariadb 10.2

sql mysql performance mariadb indexing

来源：https://stackoverflow.com/questions/62818044/optimising-query-with-multiple-selects-as-columns

2条答案

按热度按时间

91zkwejq1#

取消激活数据，然后进行聚合：

SELECT player, 
        COUNT(*) AS since_start_matches, 
        SUM(ht_total_goals=0) AS since_start_ht_0,
        SUM(ht_total_goals=1) AS since_start_ht_1,
        SUM(ht_total_goals=2) AS since_start_ht_2,
        SUM(ht_total_goals=3) AS since_start_ht_3,
        SUM(ht_total_goals=4) AS since_start_ht_4,
        SUM( ht_total_goals>=5) AS since_start_ht_5_plus
FROM ((SELECT player_1 as player, ht_total_goals
       FROM results
      ) UNION ALL
      (SELECT player_2 as player, ht_total_goals
       FROM results
      )
     ) p
GROUP BY player

赞(0）回复(0）举报 2021-07-24

ctzwtxfj2#

你可以用 LEFT JOIN 以及 conditional aggregation 具体如下：

SELECT  player_1 AS player, 
                COUNT(T2.player_1) AS since_start_matches, 
                SUM(CASE WHEN T2.ht_total_goals=0 THEN 1 END) AS since_start_ht_0,
                SUM(CASE WHEN T2.ht_total_goals=1 THEN 1 END) AS since_start_ht_1,
                SUM(CASE WHEN T2.ht_total_goals=2 THEN 1 END) AS since_start_ht_2,
                SUM(CASE WHEN T2.ht_total_goals=3 THEN 1 END) AS since_start_ht_3,
                SUM(CASE WHEN T2.ht_total_goals=4 THEN 1 END) AS since_start_ht_4,
                SUM(CASE WHEN T2.ht_total_goals>=5 THEN 1 END) AS since_start_ht_5_PLUS
FROM results T1 LEFT JOIN results T2 
ON (T2.player_1=T.player OR T2.player_2=T.player)
GROUP BY T1.PLA

按玩家排序

赞(0）回复(0）举报 2021-07-24

我来回答

使用多个selectas列优化查询

2条答案

相关问题

热门标签

最新问答