Index not used for JOIN on compound key plus WHERE range filter
Posted by:
Chad B
Date: August 15, 2013 09:24AM
Hello. I am trying to figure out why my index is not used in this scenario. Here I am providing a reduced example schema to illustrate the problem.
I have used some "real world" terms in my example schema to make it easier to read than single letter table/column names. The "place" table represents locations and the "appt" table represents appointments that may be scheduled at those locations. I threw in some VARCHAR columns too just for completeness.
In short, each table has a composite primary key (pk1, pk2). The appt table joins to the place table using the foreign key (pk1, placePk2). In other words, pk1 in both tables matches and appt.placePk2 matches against place.pk2.
CREATE TABLE `place` (
`pk1` smallint(6) NOT NULL,
`pk2` int(11) NOT NULL,
`name` varchar(45) NOT NULL,
`enable` tinyint(1) NOT NULL,
PRIMARY KEY (`pk1`,`pk2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `appt` (
`pk1` smallint(6) NOT NULL,
`pk2` int(11) NOT NULL,
`placePk2` int(11) NOT NULL,
`when` date NOT NULL,
`description` varchar(45) NOT NULL,
PRIMARY KEY (`pk1`,`pk2`),
KEY `ix_appt` (`pk1`,`placePk2`,`when`),
CONSTRAINT `fk_appt_to_place` FOREIGN KEY (`pk1`, `placePk2`) REFERENCES `place` (`pk1`, `pk2`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
EXPLAIN
SELECT place.name, appt.when, appt.description
FROM place
JOIN appt ON appt.pk1 = place.pk1 AND appt.placePk2 = place.pk2
WHERE
place.enable <> 0
AND appt.when >= '2013-08-15'
AND appt.when < '2013-08-20'
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: place
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: appt
type: ref
possible_keys: PRIMARY,ix_appt
key: PRIMARY
key_len: 2
ref: testindex.place.pk1
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
Now I realize that the optimizer uses statistics and with empty tables this may not seem like a valid test. But the thing is the same thing happens on a real database with 1,197 rows in the equivalent of the "place" table and 28,956,644 rows in "appt".
So:
I don't mind the table scan of "place". What bothers me is that for appt it's using PRIMARY instead of ix_appt.
If I were implementing this, I would say:
1. Iterate through place; for each matching row (enabled <> 0):
2. Search appt for matching pk1 and placePk2 and lower bound of "when" using ix_appt
3. Iterate forward through keys while "when" range is satisfied
Instead it does:
1. Iterate through place; for each matching row (enabled <> 0):
2. Search appt for matching pk1 using PRIMARY
3. Scan ALL of those to find ones with matching placePk2 and "when"
And the thing is, somehow I think the optimizer is right, because when I force it to do it my way by using STRAIGHT_JOIN and USE INDEX it takes longer.
BUT...
Yet another quirk: after I added the index, it behaved as described above. But this morning, a few hours later, after I typed this whole message, the same query IS NOW using the index sort of...
BUT key len is 6, so it is still not taking advantage of the "when" included in the index to help handle the WHERE clause.
Can anyone explain all this behavior or offer any suggestions for performance improvement?