[MDEV-6796] Unable to skip filesort when using implicit extended key Created: 2014-09-26  Updated: 2014-10-06  Resolved: 2014-10-06

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.13, 10.1.0
Fix Version/s: 10.1.1

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: extended-keys, optimizer, order-by-optimization


 Description   

The optimizer is able to skip filesort when PK columns are explicitly defined in the secondary index, but not able to do so when they are in the index extension.

Test dataset:

CREATE TABLE tb_bug1 (
 
  pk1 int(11) NOT NULL,
 
  pk2 varchar(64) NOT NULL,
 
  col1 varchar(16) DEFAULT NULL,
 
  PRIMARY KEY (pk1,pk2),
 
  KEY key1 (pk1,col1)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE tb_bug2 (
 
  pk1 int(11) NOT NULL,
 
  pk2 varchar(64) NOT NULL,
 
  col1 varchar(16) DEFAULT NULL,
 
  PRIMARY KEY (pk1,pk2),
 
  KEY key1 (pk1,col1,pk2)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `tb_bug1` VALUES 
(12321321,'a8f5f167f44f4964e6c998dee827110c','video'),
 
(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'),
 
(12321321,'wwafdsafdsafads','video'),
 
(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'),
 
(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'),
 
(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'),
 
(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd');
 
insert into tb_bug2 select * from tb_bug1;
 

explain SELECT pk2
 
FROM tb_bug1 USE INDEX(key1)
 
WHERE pk1 = 123 AND col1 = 'video'
 
ORDER BY pk2 DESC LIMIT 21;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  tb_bug1 ref     key1    key1    55      const,const     1       Using where; Using index; Using filesort
 
explain SELECT pk2
 
FROM tb_bug2 USE INDEX(key1)
 
WHERE pk1 = 123 AND col1 = 'video'
 
ORDER BY pk2 DESC LIMIT 21;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       SIMPLE  tb_bug2 ref     key1    key1    55      const,const     1       Using where; Using index

Note that the first query uses "Using filesort" while the second one doesn't.

 Comments   
Comment by Sergei Petrunia [ 2014-09-26 ]
  • The problem is not related to issues fixed by MDEV-6657 and MDEV-6402.
  • It is also repeatable in mysql-5.6
Comment by Sergei Petrunia [ 2014-09-26 ]

The problem is here in test_if_order_by_key() function:

      /* 
        We are at the end of the key. Check if the engine has the primary
 
        key as a suffix to the secondary keys. If it has continue to check
 
        the primary key as a suffix.
 
      */
 
      if (!on_pk_suffix && (table->key_info[idx].ext_key_part_map & 1) &&
 
          (table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
 
          table->s->primary_key != MAX_KEY &&
 
          table->s->primary_key != idx)
 
      {

Here, (table->key_info[idx].ext_key_part_map & 1) == 0. This condition is false, and I don't see any reason why we should require that it is true.

(Investigated what ext_key_part_map is. See here: http://lists.askmonty.org/pipermail/commits/2014-September/006655.html)

MySQL 5.6 has different code, but it's still incorrect. Their code doesn't take into account that secondary index may explicitly include columns from the primary index.

Comment by Sergei Petrunia [ 2014-09-26 ]

Fix pushed to the 10.1-based tree with ORDER BY optimization fixes: https://github.com/MariaDB/server/tree/bb-10.1-orderby-fixes

elenst, I need a testing pass for this fix. It is ok to test together with other fixes in 10.1-orderby-fixes.

Generated at Thu Feb 08 07:14:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.