Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8077

Add order by clause make query to 100 times longer to execute

    XMLWordPrintable

Details

    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.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            pqa Patrick Quentin Armitage
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.