mysql - 通过计数(不同)优化查询

Question

我有一个 SQL 查询，其简化版本如下所示:

SELECT
      ff.f_id AS field_id,
      COUNT(DISTINCT pca.pc_id) AS num_distinct_bundles,
      COUNT(DISTINCT pca.p_id) AS num_distinct_programs
FROM
      sometable  ff,
      sometable2 b,
      sometable3 pca
WHERE
          ff.f_id = b.f_id
      AND b.pc_id = pca.pc_id
GROUP BY
      ff.f_id;

问题是COUNT(DISTINCT) 。这需要花费很多时间。我修补了一下，发现

SELECT 
    COUNT(column)
FROM
    (SELECT DISTINCT column from tableX) as tableY

比使用 COUNT(DISTINCT) 更快直接地。我能够将部分查询编写为

SELECT
    ff.f_id AS field_id,
    COUNT(t1.pc_id) as num_distinct_bundles
FROM
    sometable ff,
    sometable2 b,
    (SELECT DISTINCT pca.pc_id FROM sometable3 AS pca) AS t1
WHERE
         ff.f_id = b.f_id
    AND  b.pc_id = t1.pc_id
GROUP BY
    ff.f_id

这将前两列的查询时间减少到了初始时间的几乎 1/4。但是我想不出一种方法来获取第二列 num_distinct_programs

可以用更好的方式来完成吗？

Answer 1

尝试一下这是否会有所不同:

SELECT
    ff.f_id AS field_id, t1.num_distinct_bundles, t2.num_distinct_programs
FROM
    sometable ff 
    INNER JOIN sometable2 b ON ff.f_id = b.f_id,
    (
    SELECT COUNT(t.pc_id) as num_distinct_bundles
    FROM (
        SELECT DISTINCT pca.pc_id FROM sometable3
        ) AS t
    ) as t1,    
    (
    SELECT COUNT(t.p_id) as num_distinct_programs
    FROM (
        SELECT DISTINCT pca.p_id FROM sometable3 
        ) AS t
    ) as t2 

GROUP BY
    ff.f_id