[MDEV-11421] Wrong index selected by optimizer for WHERE + ORDER BY Created: 2016-11-29  Updated: 2017-02-16

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.19
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Nochum Sossonko Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: order-by-optimization
Environment:

Ubuntu 14.04 LTS


 Description   

For a query like:

SELECT 
    `a`.`id`,
 
    `a`.`person`
 
FROM
 
    `parts` AS `a`
 
        LEFT JOIN
 
    `persons` AS `person` ON `a`.`person` = `person`.`id`
 
        LEFT JOIN
 
    `bosses` AS `boss` ON `boss`.`id` = `person`.`boss`
 
        LEFT JOIN
 
    `logins` AS `login` ON `login`.`id` = `boss`.`login`
 
WHERE
 
    (`login`.`type` = '2')
 
ORDER BY `a`.`id` DESC
 
LIMIT 30;

The query optimizer returns:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE login ref PRIMARY,type_KEY type_KEY 47 const 23 Using where; Using index; Using temporary; Using filesort
1 SIMPLE boss ref PRIMARY,login_KEY login_KEY 47 login.id 1 Using index
1 SIMPLE person ref PRIMARY,boss_KEY boss_KEY 47 boss.id 1 Using index
1 SIMPLE a ref person_KEY person_KEY 47 person.id 6474  

The query takes a couple of seconds to complete. If I just add an index hint to PRIMARY on the parts table (alias `a`), the query takes 0.00 sec to complete. Every attribute used in the example is indexed.

 Comments   
Comment by Elena Stepanova [ 2016-11-30 ]

Please paste the output of SHOW CREATE TABLE ... and SHOW INDEX IN ... for all involved tables.

Comment by Nochum Sossonko [ 2016-11-30 ] 

CREATE TABLE `parts` (
 
  `id` varchar(15) NOT NULL,
 
  `person` varchar(15) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `person_KEY` (`person`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `persons` (
 
  `id` varchar(15) NOT NULL,
 
  `boss` varchar(15) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `boss_KEY` (`boss`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `bosses` (
 
  `id` varchar(15) NOT NULL,
 
  `login` varchar(15) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `login_KEY` (`login`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `logins` (
 
  `id` varchar(15) NOT NULL,
 
  `username` varchar(50) NOT NULL,
 
  `type` varchar(15) NOT NULL DEFAULT '000000000000001',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `usertype_UNIQUE` (`username`,`type`),
 
  KEY `type_KEY` (`type`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
parts 0 PRIMARY 1 id A 3 NULL NULL BTREE
parts 1 person_KEY 1 person A 3 NULL NULL BTREE
persons 0 PRIMARY 1 id A 0 NULL NULL BTREE
persons 1 boss_KEY 1 boss A 0 NULL NULL BTREE
bosses 0 PRIMARY 1 id A 0 NULL NULL BTREE
bosses 1 login_KEY 1 login A 0 NULL NULL BTREE
logins 0 PRIMARY 1 id A 0 NULL NULL BTREE
logins 0 usertype_UNIQUE 1 username A 0 NULL NULL BTREE
logins 0 usertype_UNIQUE 2 type A 0 NULL NULL BTREE
logins 1 type_KEY 1 type A 0 NULL NULL BTREE
Comment by Nochum Sossonko [ 2017-01-03 ]

If I remove the the index on `logins`.`type` the plan is much better (uses `a`.`id` with no file sorting). I need that index though for other queries, so I'm really not sure what to do here. Index hinting is not really an option for me in my application.

Comment by Nochum Sossonko [ 2017-01-10 ]

If anyone can think of a workaround (outside of index hinting) I'd greatly appreciate it. Some way to get the query planner to stick with the primary and eq_ref the rest.

Comment by Nochum Sossonko [ 2017-02-16 ]

I think this is the same (or very similar) as MDEV-8306

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