| Bug #61223 | SQL_CALC_FOUND_ROWS kills performance with left join ,join,right join and LIMIT | ||
|---|---|---|---|
| Submitted: | 19 May 2011 1:57 | Modified: | 22 Oct 2012 4:51 |
| Reporter: | shu xinyi | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S5 (Performance) |
| Version: | 5.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 May 2011 3:11]
Valeriy Kravchuk
Please, send the results of EXPLAIN for the problematic SELECT with SQL_CALC_FOUND_ROWS and without this clause.
[19 May 2011 6:19]
心意 舒
explain select sql_calc_found_rows * from vreqinfos limit 1000,1000;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 2178824 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | vtms3000.a.IDeviceId | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | vtms3000.a.iMIbNodeCode | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
vreqinfos 定义如下：
CREATE ALGORITHM=UNDEFINED DEFINER=`mysql`@`%` SQL SECURITY DEFINER VIEW `vreqinfos` AS
select
`a`.`iReqInfoId` AS `iReqInfoId`,
`a`.`IDeviceId` AS `IDeviceId`,
`b`.`sDevName` AS `sDevName`,
`a`.`iReqTime` AS `iReqTime`,
`a`.`iMIbNodeCode` AS `iMibNodeCode`,
`c`.`sMibNodeName` AS `sMibNodeName`,
`a`.`sValue` AS `sValue`,
`a`.`iRowNum` AS `iRowNum`
from
((`qreqinfos` `a` left join `qdevices` `b` on((`a`.`IDeviceId` = `b`.`iDeviceId`))) left join `qsnmpmib` `c` on((`a`.`iMIbNodeCode` = `c`.`iMibNodeCode`)));
[19 May 2011 7:07]
Valeriy Kravchuk
So, your query just has to read all rows from the a table (there are no filtering conditions on it, and you left join other tables to it). Table is big enough. Why do you think there is any bug here?
[19 May 2011 11:32]
心意 舒
I make a test,comparing two cases: 0.01210275 | select * from vreqinfos limit 1000,1000 8.863353 | select sql_calc_found_rows * from vreqinfos limit 1000,1000 The first query spend 0.01210275 secs, and the second query spend 8.863353 secs. I think sql_calc_found_rows make the query performance lower.
[19 May 2011 12:22]
Valeriy Kravchuk
Please, send the results of EXPLAIN select * from vreqinfos limit 1000,1000 ; What exact server version, 5.0.x, do you use?
[20 May 2011 0:43]
心意 舒
the vreqinfos define:
CREATE ALGORITHM=UNDEFINED DEFINER=`mysql`@`%` SQL SECURITY DEFINER VIEW `vreqinfos` AS
select
`a`.`iReqInfoId` AS `iReqInfoId`,
`a`.`IDeviceId` AS `IDeviceId`,
`b`.`sDevName` AS `sDevName`,
`a`.`iReqTime` AS `iReqTime`,
`a`.`iMIbNodeCode` AS `iMibNodeCode`,
`c`.`sMibNodeName` AS `sMibNodeName`,
`a`.`sValue` AS `sValue`,
`a`.`iRowNum` AS `iRowNum`
from
((`qreqinfos` `a` left join `qdevices` `b` on((`a`.`IDeviceId` = `b`.`iDeviceId`))) left join `qsnmpmib` `c` on((`a`.`iMIbNodeCode` = `c`.`iMibNodeCode`)));
explain select * from vreqinfos limit 1000,1000;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 2178824 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | vtms3000.a.IDeviceId | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | vtms3000.a.iMIbNodeCode | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------+
the version of mysql is 5.0.51b-community-nt
[31 Dec 2011 15:18]
Valeriy Kravchuk
Please, check if the same problem still happens with a newer versions, 5.0.91 at least or (as 5.0 support ends today) 5.1.60 or 5.5.19.
[4 Jan 2012 3:30]
心意 舒
Test:
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.5 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.9-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
The version of mysql is 5.5.9-log,OS is win32.
mysql> set profiling=1;
mysql> select * from vreqinfos limit 1000,1000;
mysql> select sql_calc_found_rows * from vreqinfos limit 1000,1000;
mysql> show profiles;
+----------+-----------+-------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-----------+-------------------------------------------------------------+
| 1 | 0.0307945 | select * from vreqinfos limit 1000,1000 |
| 2 | 15.674417 | select sql_calc_found_rows * from vreqinfos limit 1000,1000 |
+----------+-----------+-------------------------------------------------------------+
The second query spend more time than the first one.The difference is very large.
I guess this is a bug.
The vreqinfos define as:
CREATE or REPLACE VIEW vreqinfos AS
select
a.iReqInfoId AS iReqInfoId,
a.IDeviceId AS IDeviceId,
b.sDevName AS sDevName,
a.iReqTime AS iReqTime,
a.iMibNodeCode AS iMibNodeCode,
c.sMibNodeName AS sMibNodeName,
a.sValue AS sValue,
a.iRowNum AS iRowNum
from
qreqinfos a left join qdevices b on
a.IDeviceId = b.iDeviceId
left join qsnmpmib c on
a.iMibNodeCode = c.iMibNodeCode;
[22 Sep 2012 4:51]
MySQL Verification Team
Please try version 5.5.27. Thanks.
[23 Oct 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: 2 | 4.2E-5 | shwo profiles | | 3 | 0.0062625 | call procquerysql('qreqinfos','',1000,1000,@countrows) | | 4 | 8.925E-5 | select @countrows | | 5 | 4.825E-5 | show profiles for query 3 | | 6 | 0.01210275 | select * from vreqinfos limit 1000,1000 | | 7 | 0.00552225 | select * from qreqinfos limit 1000,1000 | | 8 | 7E-5 | call procquerysql('vreqinfos','',1000,1000) | | 9 | 8.65064225 | call procquerysql('vreqinfos','',1000,1000,@countrows) | | 10 | 0.005523 | select * from qreqinfos limit 1000,1000 | | 11 | 8.863353 | select sql_calc_found_rows * from vreqinfos limit 1000,1000 | | 12 | 0.006778 | select sql_calc_found_rows * from qreqinfos limit 1000,1000 | | 13 | 6.425E-5 | select * from qreqinfos limit 1000,1000 order by irownum desc | | 14 | 5.95E-5 | select * from qreqinfos limit 1000,1000 order by irownum | | 15 | 1.63074725 | select * from qreqinfos order by irownum desc limit 1000,1000 | | 16 | 4.802011 | select sql_calc_found_rows * from qreqinfos order by irownum desc limit 1000,1000 in vreqinfos,used left join opeareation. there is 200,0000 rows in qreqinfos table. How to repeat: 在使用 SQL_CALC_FOUND_ROWS 的关键字做带连接运算的查询时，效率很低。