查询执行量过重 - Too heavy query execution

Too heavy query execution

本文关键字：执行查询 | 更新日期: 2023-09-27

我有一个包含 187840 行的数据库。当我执行此查询时，我收到此消息查询执行已中断

查询太重

SELECT days.day,count(U.sig_name) as number 
FROM days 
LEFT JOIN linked U ON 
    days.day = date(timestamp) 
    AND 
    U.sig_name REGEXP  "^Tester" 
GROUP BY days.day;

解决方案是什么？

这是您的查询：

select days.day, count(U.sig_name) as number
from days left join
     linked U
     on days.day = date(timestamp) AND U.sig_name REGEXP "^Tester"
group by days.day;

由于timestamp周围的函数调用，您遇到了问题。您可能会发现此版本更好：

select days.day,
       (select count(*)
        from linked u
        where u.timestamp >= days.day an du.timestamp < date_add(days.day, interval 1 day) and
              u.sig_name not like '%Tester%'
       )
from days;

为了提高性能，您需要在 linked(timestamp, sig_name) 上有一个复合索引。这将消除外部聚合（聚合改用索引），并允许使用索引进行匹配。

您可以使用

LIMIT 处理海量数据：

$limit_size = 10000;
        $flag_done = false;
        for ($i = 1; ! $flag_done; $i++) {
            $queryString = "SELECT days.day,count(U.sig_name) as number from days left join linked U on days.day = date(timestamp)  AND U.sig_name REGEXP  "^Tester" group by days.day LIMIT $index*$limit_size, $limit_size";
            if($result = mysql_query($queryString, $db)){
                [WHAT YOU WANT TO DO WITH RESULT HERE]
            } else $flag_done = true;
        }