时间戳

首页 > 解决方案 > 如何制作具有更好性能的 COUNT？

mysql - 如何制作具有更好性能的 COUNT？

问题描述

我正在使用这个查询，它需要 3 秒才能运行。表通告有 255k 行，表地址有 30k 行。

SELECT COUNT(DISTINCT ad.id) as total_address
FROM (
  SELECT
    ad.id, an.id as announce_id,
    an.buy_price as buy_price,
    an.rental_price as rental_price,
    (SELECT Count(*) FROM owner_address 
      WHERE address_id = ad.id) as totalOwnersAd,
    (SELECT Count(*) FROM owner_announce 
      WHERE announce_id = an.id) as totalOwnersAn
  FROM addresses ad
  RIGHT JOIN announces an ON ad.id = an.address_id
  WHERE an.name = 'tralala' and ad.name = 'tralala'
) ad

和子查询：

SELECT
  ad.id, an.id as announce_id,
  an.buy_price as buy_price,
  an.rental_price as rental_price,
  (SELECT Count(*) FROM owner_address 
    WHERE address_id = ad.id) as totalOwnersAd,
  (SELECT Count(*) FROM owner_announce 
    WHERE announce_id = an.id) as totalOwnersAn
FROM addresses ad
RIGHT JOIN announces an ON ad.id = an.address_id
WHERE an.name = 'tralala' and ad.name = 'tralala'

运行需要 17 毫秒。

为什么我COUNT的需要这么长时间？

我正在使用 MySql。

由于我的动态过滤器，我需要加入announces和表格。addresses例如，当我需要计算公告列 X 等于 Y 的地址时。

编辑：

我试过了：

SELECT COUNT(DISTINCT ad.address_id) as total_address
        FROM announces an JOIN ( 
           SELECT adds.id as address_id
                FROM (
                      SELECT id,
                      (SELECT Count(*) FROM owner_address WHERE address_id = ad.id) as totalOwnersAd
                      from addresses ad


             )  as adds
        ) as ad ON ad.address_id = an.address_id
        WHERE 
         ( an.bedroom = 2 )

它快了 10%，但仍然需要很长时间。当我有地址过滤器时它会更快。

反正有索引子查询表吗？

标签： mysqlsqlquery-optimizationsql-optimization

解决方案

我只是好奇你为什么不使用：

select count(distinct an.address_id)
from announces;

达到相同值似乎是一种更简单的方法。

编辑：

对于您修改后的查询，这更简单：

SELECT COUNT(DISTINCT an.address_id)
FROM addresses ad JOIN
     announces an
     ON ad.id = an.address_id
WHERE an.name = 'tralala' and ad.name = 'tralala';

是不必要的right join，因为WHERE条件将它变成了inner join. 子查询和附加列不是必需的。

推荐阅读