[MDEV-23487] query analizer chooses the wrong index where it should obviously choose another Created: 2020-08-14  Updated: 2020-09-30

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.5
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Sean Garrett Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

any environment, tested both on Linux and windows


 Description   

I have two tables, this is a financial application so performance is paramount.
here are the two tables.

CREATE TABLE `hl_b` (
 
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
	`c_id` INT(11) UNSIGNED NOT NULL,
 
	`a_id` INT(11) UNSIGNED NOT NULL,
 
	`fai` VARCHAR(20) NULL DEFAULT NULL,
 
	`is_id` INT(11) NULL DEFAULT NULL,
 
	`nm` VARCHAR(11) NOT NULL,
 
	`u` DECIMAL(10,4) UNSIGNED NULL DEFAULT NULL,
 
	`u_a` DECIMAL(10,4) UNSIGNED NULL DEFAULT NULL,
 
	`do` VARCHAR(1) NULL DEFAULT NULL,
 
	`lpd` DATE NULL DEFAULT NULL,
 
	PRIMARY KEY (`id`),
 
	INDEX `fai` (`c_id`, `is_id`, `fai`),
 
	INDEX `a_id` (`c_id`, `a_id`, `is_id`, `nm`, `fai`)
 
)
 
COLLATE='utf8_general_ci'
 
ENGINE=InnoDB
 
ROW_FORMAT=DYNAMIC
 
AUTO_INCREMENT=8
 
;
 
 
 
CREATE TABLE `a` (
 
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
	`c_id` INT(11) UNSIGNED NULL DEFAULT NULL,
 
	`typ` VARCHAR(7) NULL DEFAULT NULL,
 
	`gr` BIT(1) NULL DEFAULT NULL,
 
	PRIMARY KEY (`id`)
 
)
 
COLLATE='utf8_general_ci'
 
ENGINE=InnoDB
 
ROW_FORMAT=DYNAMIC
 
AUTO_INCREMENT=39
 
;

now look at this query:

EXPLAIN 
SELECT h.a_id, a.typ from hl_b h inner join a a ON a.id = h.a_id WHERE h.c_id = 1 AND h.is_id = 9 AND h.fai = '12345' AND a.typ = 'AB'

please note the where clause:
h.c_id = 1 AND h.is_id = 9 AND h.fai = '12345'

this is an exact match for "fai" index, but it keeps choosing the "a_id" index
no matter how I change the query it always uses a_id index.

and I think I found out why, it seems like because I am selecting "h.a_id", no matter what, it chooses the a_id index because this index has "a_id" column in it even though the where clause very obviously says to use 'fai' index.

now to prove this point if I choose a different colunm in the query (please note the where clause is exactly the same, only selecting a different column h.u):

EXPLAIN 
SELECT h.u, a.typ from hl_b h inner join a a ON a.id = h.a_id WHERE h.c_id = 1 AND h.is_id = 9 AND h.fai = '12345' AND a.typ = 'AB'

it chooses the correct index (fai).

so I'm not sure why, maybe it is how it should be but to me it seems like pretty obvious.

my database has millions of records so this could be an issue in a large database, currently I'm forcing the query to use fai index (use index (fai)).

thanks for your attention
Sean
Generated at Thu Feb 08 09:22:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.