Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.5
-
None
-
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