To summarize, there are several simple cases where MySQL optimiser doesn't select the right index. This entails strong performance issues (especially when filesorting is involved), and force the developers to hack their applications, using USE INDEX (when it's possible...).
Hoping someone finally takes a look at this serious issue...
Actually it could also help some of my customers to switch to mariadb, because they are also a little bit tired to put USE INDEX everywhere in there code
Thanks and regards,
Jocelyn Fournier
Attachments
Issue Links
duplicates
MDEV-626LP:639949 - Non optimal index choice, depending on index creation order
Closed
relates to
MDEV-6384It seems like OPTIMIZER take into account the order of indexes in the table.
looks weird.
Why a range access is used here with id2_2, instead of ref ? (although no more filesort is used, which is great !)
jocelyn fournier
added a comment - Hi Sergei,
The explain
MariaDB [j11]> EXPLAIN SELECT id1 FROM a WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | a | range | id2,id2_2 | id2_2 | 2 | NULL | 3 | Using where |
+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
looks weird.
Why a range access is used here with id2_2, instead of ref ? (although no more filesort is used, which is great !)
range and ref(const) are for the most part the same thing. Code for range access is able to scan arbitrary ranges, code for ref access handles lookups on tbl.key=expr, where expr can also be constant.
Internally, ref(const) is converted into range in most cases (or maybe even all cases).
Here, we use index to read rows in order. We can use arbitrary range access, but not arbitrary ref access. that's why ORDER BY optimizer switches to range access. ref access is constructed by the join optimizer. however, ORDER BY optimizer is invoked after join optimizer, so range does not get replaced with ref. But, as I said, there is hardly any difference.
Sergei Petrunia
added a comment - range and ref(const) are for the most part the same thing. Code for range access is able to scan arbitrary ranges, code for ref access handles lookups on tbl.key=expr, where expr can also be constant.
Internally, ref(const) is converted into range in most cases (or maybe even all cases).
Here, we use index to read rows in order. We can use arbitrary range access, but not arbitrary ref access. that's why ORDER BY optimizer switches to range access. ref access is constructed by the join optimizer. however, ORDER BY optimizer is invoked after join optimizer, so range does not get replaced with ref. But, as I said, there is hardly any difference.
Bug
Minor
Trying the original testcase from http://bugs.mysql.com/bug.php?id=36817:
Clean MariaDB 10.1:
MariaDB [j11]> CREATE TABLE `a` (-> `id1` int(10) unsigned NOT NULL auto_increment,-> `id2` tinyint(3) unsigned NOT NULL default '0',-> `id3` tinyint(3) unsigned NOT NULL default '0',-> `id4` int(10) unsigned NOT NULL default '0',-> `date` timestamp NOT NULL default CURRENT_TIMESTAMP,-> PRIMARY KEY (`id1`),-> KEY `id2` (`id2`,`id3`,`id4`,`date`),-> KEY `id2_2` (`id2`,`id3`,`date`)-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.08 sec)MariaDB [j11]> INSERT INTO a (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);Query OK, 7 rows affected (0.01 sec)Records: 7 Duplicates: 0 Warnings: 0MariaDB [j11]> EXPLAIN SELECT id1 FROM a WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;+------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------------+| 1 | SIMPLE | a | ref | id2,id2_2 | id2 | 2 | const,const | 3 | Using where; Using filesort |+------+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------------+MariaDB [j11]> DROP TABLE IF EXISTS a;MariaDB [j11]> CREATE TABLE `a` (-> `id1` int(10) unsigned NOT NULL auto_increment,-> `id2` tinyint(3) unsigned NOT NULL default '0',-> `id3` tinyint(3) unsigned NOT NULL default '0',-> `id4` int(10) unsigned NOT NULL default '0',-> `date` timestamp NOT NULL default CURRENT_TIMESTAMP,-> PRIMARY KEY (`id1`),-> KEY `id2` (`id2`,`id3`,`date`),-> KEY `id2_2` (`id2`,`id3`,`id4`,`date`)-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.06 sec)MariaDB [j11]> INSERT INTO a (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0MariaDB [j11]> EXPLAIN SELECT id1 FROM a WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| 1 | SIMPLE | a | ref | id2,id2_2 | id2 | 2 | const,const | 3 | Using where |+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+10.1 tree with fixes for
MDEV-6384,MDEV-6480:MariaDB [j11]> CREATE TABLE `a` (-> `id1` int(10) unsigned NOT NULL auto_increment,-> `id2` tinyint(3) unsigned NOT NULL default '0',-> `id3` tinyint(3) unsigned NOT NULL default '0',-> `id4` int(10) unsigned NOT NULL default '0',-> `date` timestamp NOT NULL default CURRENT_TIMESTAMP,-> PRIMARY KEY (`id1`),-> KEY `id2` (`id2`,`id3`,`id4`,`date`),-> KEY `id2_2` (`id2`,`id3`,`date`)-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.12 sec)MariaDB [j11]> INSERT INTO a (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0MariaDB [j11]> EXPLAIN SELECT id1 FROM a WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+| 1 | SIMPLE | a | range | id2,id2_2 | id2_2 | 2 | NULL | 3 | Using where |+------+-------------+-------+-------+---------------+-------+---------+------+------+-------------+MariaDB [j11]> DROP TABLE IF EXISTS a;Query OK, 0 rows affected (0.00 sec)MariaDB [j11]> CREATE TABLE `a` (-> `id1` int(10) unsigned NOT NULL auto_increment,-> `id2` tinyint(3) unsigned NOT NULL default '0',-> `id3` tinyint(3) unsigned NOT NULL default '0',-> `id4` int(10) unsigned NOT NULL default '0',-> `date` timestamp NOT NULL default CURRENT_TIMESTAMP,-> PRIMARY KEY (`id1`),-> KEY `id2` (`id2`,`id3`,`date`),-> KEY `id2_2` (`id2`,`id3`,`id4`,`date`)-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;Query OK, 0 rows affected (0.07 sec)MariaDB [j11]> INSERT INTO a (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1);Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0MariaDB [j11]> EXPLAIN SELECT id1 FROM a WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4;+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+| 1 | SIMPLE | a | ref | id2,id2_2 | id2 | 2 | const,const | 3 | Using where |+------+-------------+-------+------+---------------+------+---------+-------------+------+-------------+