Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.1.67, 5.2.14
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Rows_examined in the slow query log can be reset by the outer query when using a sub query. For these two queries the first one sets Rows_examined correctly but the second one has it reset even though the row stats work correctly.
# Time: 110702 9:44:23 # User@Host: [ebergen] @ localhost [] # Thread_id: 1 Schema: test QC_hit: No # Query_time: 1.396374 Lock_time: 0.000093 Rows_sent: 1 Rows_examined: 677984 # Row_Stats: test:rows_read=677984,rows_changed=0,rows_changed_x_indexes=0; # Index_Stats: test:rows_read=677984;
SET timestamp=1309625063; select count(*) from t2;
# Time: 110702 9:44:49 # User@Host: [ebergen] @ localhost [] # Thread_id: 1 Schema: test QC_hit: No # Query_time: 2.262701 Lock_time: 0.000126 Rows_sent: 1 Rows_examined: 0 # Row_Stats: test:rows_read=677984,rows_changed=0,rows_changed_x_indexes=0;#temp#:rows_read=0,rows_changed=677984,rows_changed_x_indexes=677984; # Index_Stats: test:rows_read=677984;
SET timestamp=1309625089; select count(*) from (select 1 from t2) as t;
The table I'm using looks like:
CREATE TABLE `t2` ( `t` bigint(20) DEFAULT NULL, `u` bigint(20) DEFAULT NULL, `b` text, KEY `t` (`t`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reproducible on MariaDB 5.1 and 5.2, but not on 5.3:
MariaDB Version 5.2.14
CREATE TABLE `t2` (
`t` bigint(20) DEFAULT NULL,
`u` bigint(20) DEFAULT NULL,
`b` text,
KEY `t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d');
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL long_query_time = 0.0001;
select count
from t2;
count
8192
select count from (select 1 from t2) as t;
count
8192
select start_time, rows_examined, sql_text from mysql.slow_log;
start_time rows_examined sql_text
2013-05-01 17:39:13 8192 select count from t2
2013-05-01 17:39:13 0 select count from (select 1 from t2) as t
MariaDB Version 5.3.12
CREATE TABLE `t2` (
`t` bigint(20) DEFAULT NULL,
`u` bigint(20) DEFAULT NULL,
`b` text,
KEY `t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,1,'a'),(2,2,'b'),(3,3,'c'),(4,4,'d');
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t2;
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL long_query_time = 0.0001;
select count from t2;
count
8192
select count from (select 1 from t2) as t;
count
8192
select start_time, rows_examined, sql_text from mysql.slow_log;
start_time rows_examined sql_text
2013-05-01 17:39:32.628289 8192 select count from t2
2013-05-01 17:39:32.731437 8192 select count from (select 1 from t2) as t
Fixed in 5.3 and further, I suppose it's good enough, not critical to fix it in 5.1 and 5.2.
Launchpad bug id: 807198