当尝试执行此查询时，我的 mysql 服务器 cpu 使用率达到 100%，并且页面停止运行。我在（Client_Code、Date_Time、Time_Stamp、Activity_Code、Employee_Name、ID_Transaction）上设置了索引，但似乎没有帮助。接下来我可以采取哪些步骤来解决此问题？此外，数据库上已经有一个索引（如果有的话）。谢谢
这是这个查询的作用
数据库信息
ID_Transaction | Client_Code | Employee_Name | Date_Time |Time_Stamp| Activity_Code
1 | 00001 | Eric | 11/15/10| 7:30AM | 00023
2 | 00001 | Jerry | 11/15/10| 8:30AM | 00033
3 | 00002 | Amy | 11/15/10| 9:45AM | 00034
4 | 00003 | Jim | 11/15/10| 10:30AM | 00063
5 | 00003 | Ryan | 11/15/10 | 12:00PM | 00063
6 | 00003 | bill | 11/14/10 | 1:00pm | 00054
7 | 00004 | Jim | 11/15/10 | 1:00pm | 00045
8 | 00005 | Jim | 11/15/10| 10:00 AM| 00045
该查询获取上面的信息并像这样进行计数。按每个 client_code 的最新条目。在这种情况下，查询将如下所示。在 PHP 后。
Jerry = 1
2 | 00001 | Jerry | 11/15/10| 8:30AM | 00033
Amy = 1
3 | 00002 | Amy | 11/15/10| 9:45AM | 00034
Ryan = 1
5 | 00003 | Ryan | 11/15/10 | 12:00PM | 00063
Jim = 2
7 | 00004 | Jim | 11/15/10 | 1:00pm | 00045
8 | 00005 | Jim | 11/15/10| 10:00 AM| 00045
$sql = "SELECT m.Employee_Name, count(m.ID_Transaction)
FROM ( SELECT DISTINCT Client_Code FROM Transaction)
md JOIN Transaction m ON
m.ID_Transaction = ( SELECT
ID_Transaction FROM Transaction mi
WHERE mi.Client_Code = md.Client_Code AND Date_Time=CURdate() AND Time_Stamp!='' AND
Activity_Code!='000001'
ORDER BY m.Employee_Name DESC, mi.Client_Code DESC, mi.Date_Time DESC,
mi.ID_Transaction DESC LIMIT 1 )
group by m.Employee_Name";
有没有更好的方法来编写此查询，以免它使我的系统陷入困境？该查询对于 10 个数据库条目运行良好，但当数据库有 300,000 个条目时它会锁定我的服务器。
谢谢
埃里克
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 8 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | m | index | [NULL] | search index | 924 | [NULL] | 21 | 100.00 | Using where; Using index; Using join buffer |
| 3 | DEPENDENT SUBQUERY | mi | ref | search index,secondary | search index | 18 | md.Client_Code | 3 | 100.00 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | Transaction | index | [NULL] | secondary | 918 | [NULL] | 21 | 38.10 | Using index |
+----+--------------------+-------------+--------+------------------------+--------------+---------+----------------+------+----------+----------------------------------------------+
