时间戳

首页 > 解决方案 > 查询运行时间过长

mysql - 查询运行时间过长

问题描述

我有这个疑问。它的作用是在给定的日期范围和其他条件下获取错误 ID 和完成这些错误所需的时间。这需要太多时间来处理。表错误，bugs_activity 由大量数据组成。

my $query = "SELECT COUNT(b.bug_id) as cntBugId, SUM(TIME_TO_SEC(TIMEDIFF(ba.bug_when,b.creation_ts)))/60
                 AS time_taken FROM techzilla.bugs b, techzilla.bugs_activity ba WHERE b.bug_id=ba.bug_id
                 AND b.bug_status='RESOLVED' AND b.resolution='FIXED' AND ba.added='RESOLVED' AND b.creation_ts
                 BETWEEN '$from' AND '$to' AND b.bug_id IN (SELECT DISTINCT b.bug_id
                 FROM techzilla.bugs b, techzilla.bugs_activity ba, techzilla.user_group_map_stats u,
                 techzilla.profiles p WHERE ba.bug_id=b.bug_id AND b.bug_status='RESOLVED' AND b.resolution='FIXED'
                 AND ba.added='RESOLVED' AND b.creation_ts BETWEEN '$from' AND '$to' AND p.userid=b.assigned_to
                 AND p.userid=u.user_id AND u.group_id='$groupId')";

查询运行：

mysql> SELECT COUNT(b.bug_id) as cntBugId, SUM(TIME_TO_SEC(TIMEDIFF(ba.bug_when,b.creation_ts)))/60 AS time_taken FROM techzilla.bugs b, techzilla.bugs_activity ba WHERE b.bug_id=ba.bug_id AND b.bug_status='RESOLVED' AND b.resolution='FIXED' AND ba.added='RESOLVED' AND b.creation_ts BETWEEN '2020-12-17 00:00:00' AND '2021-01-09 23:59:59' AND b.bug_id IN (SELECT DISTINCT b.bug_id FROM techzilla.bugs b, techzilla.bugs_activity ba, techzilla.user_group_map_stats u, techzilla.profiles p WHERE ba.bug_id=b.bug_id AND b.bug_status='RESOLVED' AND b.resolution='FIXED' AND ba.added='RESOLVED' AND b.creation_ts BETWEEN '2020-12-17 00:00:00' AND '2021-01-09 23:59:59' AND p.userid=b.assigned_to AND p.userid=u.user_id AND u.group_id='106');
+----------+--------------+
| cntBugId | time_taken   |
+----------+--------------+
|    12249 | 1739767.0167 | 
+----------+--------------+
1 row in set (7.74 sec)

表bugs由许多具有主键 bug_id 的条目组成。表包含一个人对存储在表bugs_activity中的错误所做的更改。bugs查询bugs根据错误创建日期等遍历表中列出的所有错误

每个错误都属于具有唯一 ID 的组。

有什么办法可以让这个查询运行得更快？通过加入什么的。

标签： mysqlsql

解决方案

It's very unlikely that the fact you're using Perl has any bearing on this problem (and, of course, you can demonstrate that by running the query in the mysql command line program instead of through a Perl program).

I recommend reading the MySQL documentation on optimising queries. I suspect you'll end up adding indexes to your tables.

推荐阅读