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

Performance degradation on a query with joins and ORDER BY .. LIMIT

    XMLWordPrintable

    Details

      Description

      Observed on bb-10.1-orderby-fixes commit f8f8a59c189254baeb7f90920b6b23da227984e8

      The query:

      SELECT alias2.pk 
        FROM A 
        LEFT JOIN B AS alias1 
        INNER JOIN B AS alias2 
          ON alias1.col_int_key = alias2.col_int_key 
          ON alias2.col_varchar_10_latin1 = A.col_varchar_1024_utf8_key 
      ORDER BY alias2.pk LIMIT 10;

      Execution times and EXPLAINs
      bb-10.1-orderby-fixes: 0.20 sec

      +------+-------------+--------+-------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      | id   | select_type | table  | type  | possible_keys | key         | key_len | ref                     | rows | filtered | Extra                           |
      +------+-------------+--------+-------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      |    1 | SIMPLE      | A      | ALL   | NULL          | NULL        | NULL    | NULL                    |  100 |   100.00 | Using temporary; Using filesort |
      |    1 | SIMPLE      | alias2 | range | col_int_key   | col_int_key | 5       | NULL                    |   59 |   100.00 | Using where                     |
      |    1 | SIMPLE      | alias1 | ref   | col_int_key   | col_int_key | 5       | test.alias2.col_int_key |    1 |   100.00 | Using index                     |
      +------+-------------+--------+-------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      3 rows in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                      |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`alias2`.`pk` AS `pk` from `test`.`A` left join (`test`.`B` `alias1` join `test`.`B` `alias2`) on(((`test`.`alias1`.`col_int_key` = `test`.`alias2`.`col_int_key`) and (convert(`test`.`alias2`.`col_varchar_10_latin1` using utf8) = `test`.`A`.`col_varchar_1024_utf8_key`) and (`test`.`alias2`.`col_int_key` is not null))) where 1 order by `test`.`alias2`.`pk` limit 10 |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      10.1: 0.08 sec

      +------+-------------+--------+------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      | id   | select_type | table  | type | possible_keys | key         | key_len | ref                     | rows | filtered | Extra                           |
      +------+-------------+--------+------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      |    1 | SIMPLE      | A      | ALL  | NULL          | NULL        | NULL    | NULL                    |  100 |   100.00 | Using temporary; Using filesort |
      |    1 | SIMPLE      | alias2 | ALL  | col_int_key   | NULL        | NULL    | NULL                    |  100 |   100.00 | Using where                     |
      |    1 | SIMPLE      | alias1 | ref  | col_int_key   | col_int_key | 5       | test.alias2.col_int_key |    1 |   100.00 | Using index                     |
      +------+-------------+--------+------+---------------+-------------+---------+-------------------------+------+----------+---------------------------------+
      3 rows in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                      |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`alias2`.`pk` AS `pk` from `test`.`A` left join (`test`.`B` `alias1` join `test`.`B` `alias2`) on(((`test`.`alias1`.`col_int_key` = `test`.`alias2`.`col_int_key`) and (convert(`test`.`alias2`.`col_varchar_10_latin1` using utf8) = `test`.`A`.`col_varchar_1024_utf8_key`) and (`test`.`alias2`.`col_int_key` is not null))) where 1 order by `test`.`alias2`.`pk` limit 10 |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      With increasing the number of rows in tables the approximate ratio holds.

      Tables:

      CREATE TABLE `A` (
        `col_datetime` datetime DEFAULT NULL,
        `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
        `col_datetime_key` datetime DEFAULT NULL,
        `col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
        `col_int_key` int(11) DEFAULT NULL,
        `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
        `col_date_key` date DEFAULT NULL,
        `pk` int(11) NOT NULL AUTO_INCREMENT,
        `col_varchar_1024_latin1` varchar(1024) DEFAULT NULL,
        `col_date` date DEFAULT NULL,
        `col_varchar_1024_latin1_key` varchar(1024) DEFAULT NULL,
        `col_varchar_10_latin1_key` varchar(10) DEFAULT NULL,
        `col_int` int(11) DEFAULT NULL,
        `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
        `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
        PRIMARY KEY (`pk`),
        KEY `col_datetime_key` (`col_datetime_key`),
        KEY `col_varchar_1024_utf8_key` (`col_varchar_1024_utf8_key`(333)),
        KEY `col_int_key` (`col_int_key`),
        KEY `col_date_key` (`col_date_key`),
        KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(1000)),
        KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`),
        KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`)
      ) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name                    | Seq_in_index | Column_name                 | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | A     |          0 | PRIMARY                     |            1 | pk                          | A         |         100 |     NULL | NULL   |      | BTREE      |         |               |
      | A     |          1 | col_datetime_key            |            1 | col_datetime_key            | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_varchar_1024_utf8_key   |            1 | col_varchar_1024_utf8_key   | A         |         100 |      333 | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_int_key                 |            1 | col_int_key                 | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_date_key                |            1 | col_date_key                | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_varchar_1024_latin1_key |            1 | col_varchar_1024_latin1_key | A         |         100 |     1000 | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_varchar_10_latin1_key   |            1 | col_varchar_10_latin1_key   | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | A     |          1 | col_varchar_10_utf8_key     |            1 | col_varchar_10_utf8_key     | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      CREATE TABLE `B` (
        `col_int` int(11) DEFAULT NULL,
        `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
        `col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
        `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
        `col_varchar_10_latin1` varchar(10) DEFAULT NULL,
        `col_datetime` datetime DEFAULT NULL,
        `col_date_key` date DEFAULT NULL,
        `pk` int(11) NOT NULL AUTO_INCREMENT,
        `col_varchar_1024_latin1_key` varchar(1024) DEFAULT NULL,
        `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
        `col_int_key` int(11) DEFAULT NULL,
        `col_varchar_10_latin1_key` varchar(10) DEFAULT NULL,
        `col_datetime_key` datetime DEFAULT NULL,
        `col_date` date DEFAULT NULL,
        `col_varchar_1024_latin1` varchar(1024) DEFAULT NULL,
        PRIMARY KEY (`pk`),
        KEY `col_varchar_1024_utf8_key` (`col_varchar_1024_utf8_key`(255)),
        KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`),
        KEY `col_date_key` (`col_date_key`),
        KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(767)),
        KEY `col_int_key` (`col_int_key`),
        KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`),
        KEY `col_datetime_key` (`col_datetime_key`)
      ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name                    | Seq_in_index | Column_name                 | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | B     |          0 | PRIMARY                     |            1 | pk                          | A         |         100 |     NULL | NULL   |      | BTREE      |         |               |
      | B     |          1 | col_varchar_1024_utf8_key   |            1 | col_varchar_1024_utf8_key   | A         |         100 |      255 | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_varchar_10_utf8_key     |            1 | col_varchar_10_utf8_key     | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_date_key                |            1 | col_date_key                | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_varchar_1024_latin1_key |            1 | col_varchar_1024_latin1_key | A         |         100 |      767 | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_int_key                 |            1 | col_int_key                 | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_varchar_10_latin1_key   |            1 | col_varchar_10_latin1_key   | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      | B     |          1 | col_datetime_key            |            1 | col_datetime_key            | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |
      +-------+------------+-----------------------------+--------------+-----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      The dump is attached.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

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