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

Optimization of IN (....) for multi-column comparisons

        Details

        • Type: Task
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Fix Version/s: 10.1
        • Component/s: None
        • Labels:

          Description

          we can merge this from 5.6 or implement it differently, if needed

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                Matt74 Seunguck Lee added a comment - - edited

                It seems that MySQL 5.6 doesn't have this optimization yet.

                CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`dept_no`,`emp_no`),
  KEY `ix_fromdate` (`from_date`),
  KEY `ix_empno_fromdate` (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> explain select * from dept_emp where (emp_no, dept_no) in ( (10017,'d001'), (10208, 'd001'));
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | dept_emp | ALL  | NULL          | NULL | NULL    | NULL | 331143 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.15-log |
+------------+
1 row in set (0.00 sec)
                Show
                Matt74 Seunguck Lee added a comment - - edited It seems that MySQL 5.6 doesn't have this optimization yet. CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`dept_no`,`emp_no`), KEY `ix_fromdate` (`from_date`), KEY `ix_empno_fromdate` (`emp_no`,`from_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> explain select * from dept_emp where (emp_no, dept_no) in ( (10017,'d001'), (10208, 'd001')); +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | dept_emp | ALL | NULL | NULL | NULL | NULL | 331143 | Using where | +----+-------------+----------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.15-log | +------------+ 1 row in set (0.00 sec)

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    serg Sergei Golubchik
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: