Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
Fedora 22
Description
The query
select * from fd_peal where fid in (select fid from bert where sid is null) ;
takes 0.7 seconds to execute.
The query
select * from fd_peal where fid in (select fid from bert where sid is null) order by rung;
(i.e. adding order by rung) takes between 70 and 90 seconds to execute.
The query returns 94 rows, the table fd_peal has 335154 rows, and the table bert has 335137 rows.
The execution plans for the queries are:
MariaDB [db]> explain extended select * from fd_peal where fid in (select fid from bert where sid is null) order by rung;
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+----------------+
|
| 1 | PRIMARY | fd_peal | ALL | NULL | NULL | NULL | NULL | 311567 | 100.00 | Using filesort |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | MATERIALIZED | bert | ALL | NULL | NULL | NULL | NULL | 357018 | 100.00 | Using where |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+----------------+
|
|
|
MariaDB [db]> explain extended select * from fd_peal where fid in (select fid from bert where sid is null) ;
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+-------------+
|
| 1 | PRIMARY | fd_peal | ALL | NULL | NULL | NULL | NULL | 311567 | 100.00 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | MATERIALIZED | bert | ALL | NULL | NULL | NULL | NULL | 357018 | 100.00 | Using where |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+--------+----------+-------------+
|
The tables are defined as follows:
show create table fd_peal;
|
| fd_peal | CREATE TABLE `fd_peal` (
|
`fid` int(11) NOT NULL,
|
`flag` enum('OK','BAD_TOWER','FALSE','WITHDRAWN','UNACCEPTABLE','BAD_METHOD','HOAX','DUPLICATE','BAD_DATE','','ERROR','NON-COMPLIANT') DEFAULT NULL,
|
`tid` smallint(6) DEFAULT NULL,
|
`rung` date DEFAULT NULL,
|
`seq` int(11) DEFAULT NULL,
|
`method` varchar(255) DEFAULT NULL,
|
`comment` varchar(255) DEFAULT NULL,
|
`changed` varchar(255) DEFAULT NULL,
|
`camp` int(11) DEFAULT NULL,
|
KEY `xtid` (`tid`),
|
KEY `xrung` (`rung`),
|
FULLTEXT KEY `method` (`method`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
|
|
MariaDB [db]> show create table bert;
|
| bert | CREATE TABLE `bert` (
|
`fid` int(11) NOT NULL,
|
`flag` enum('OK','BAD_TOWER','FALSE','WITHDRAWN','UNACCEPTABLE','BAD_METHOD','HOAX','DUPLICATE','BAD_DATE','','ERROR','NON-COMPLIANT') DEFAULT NULL,
|
`tid` smallint(6) DEFAULT NULL,
|
`rung` date DEFAULT NULL,
|
`seq` int(11) DEFAULT NULL,
|
`method` varchar(255) DEFAULT NULL,
|
`mid` smallint(6) DEFAULT NULL,
|
`sid` smallint(3) DEFAULT NULL,
|
`vc` tinyint(1) NOT NULL DEFAULT '0',
|
`methods` smallint(4) NOT NULL DEFAULT '1',
|
`variations` smallint(4) NOT NULL DEFAULT '0',
|
`comment` varchar(255) DEFAULT NULL,
|
`changed` varchar(255) DEFAULT NULL,
|
`camp` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
I would have expected to order by clause to sort the rows returned by the query, whereas, given the amount of time the query is taking it is presumably doing the order by before the query.