I have a slow query and I don't understand why the DB is choosing to perform a sub-optimal execution plan. Server is MariaDB 5.5
Slow query (4-5 seconds) is:
SELECT VisitDate AS LASTDATE
FROM Visit
JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
Donor.autoid = Visit.autoid AND
LName LIKE '%%' AND
FName LIKE '%bob%' AND
Donor.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1
EXPLAIN'd
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Donor const PRIMARY PRIMARY 8 const 1 ""
1 SIMPLE Visit index Visit_autoid_index Visit_VisitDate_index 4 1 Using where
Fast query (sub 100ms) is:
SELECT VisitDate AS LASTDATE
FROM Visit
JOIN Donor ON Donor.autoid = Visit.autoid
WHERE
Donor.autoid = Visit.autoid AND
LName LIKE '%%' AND
FName LIKE '%bob%' AND
Visit.autoid = '1234'
ORDER BY VisitDate DESC
LIMIT 1
EXPLAIN'd
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Visit ref Visit_autoid_index Visit_autoid_index 12 const 1 Using index condition; Using where; Using filesort
1 SIMPLE Donor eq_ref PRIMARY PRIMARY 8 NewDb.Visit.autoid 1 Using where
Donor Table (partial)
CREATE TABLE `Donor` (
`autoid` bigint(20) NOT NULL AUTO_INCREMENT,
...
`FName` varchar(15) DEFAULT '',
`LName` varchar(25) NOT NULL,
...
PRIMARY KEY (`autoid`),
...
) ENGINE=InnoDB AUTO_INCREMENT=3010 DEFAULT CHARSET=latin1
Visit Table (partial)
CREATE TABLE `Visit` (
`VisitRecnum` int(11) NOT NULL AUTO_INCREMENT,
...
`autoid` varchar(10) NOT NULL,
`VisitDate` date DEFAULT NULL,
...
PRIMARY KEY (`VisitRecnum`),
KEY `Visit_autoid_index` (`autoid`),
...
KEY `Visit_VisitDate_index` (`VisitDate`),
...
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
Donor.autoid is a primary key and Visit.autoid has an index (a sort of poorly defined foreign key). VisitDate has an index.
It's like the query plan is ignoring the optimization it can make to ensure that Donor.autoid and Visit.autoid match, and scan a smaller set of rows where both autoid = '1234'.
EXPLAIN ANALYZE
for the queries in question, as well as clarification as to which table all the columns come from, and any indexes related to the columns in the query (in detail).LName
andFName
columns inDonor
? IsVisit.autoid
a primary key, or is it included in the index onVisitDate
? If the only columns fromVisit
that are used happen to both be in an index, then that index could be used as a covering index, and the DB engine wouldn't have to read in the data from the actualVisit
table itself at all. Conversely, an index onDonor.autoid
isn't enough to identify the rows needed from theDonor
table - it also has to evaluateLName
andFName
to figure out if a row belongs....Donor
andVisit
, it's unlikely that generating every possible matched pair of rows is a good first step in sorting things out; if it's going to narrow things down, and the only details it has are for Donor, then it's going to narrow down Donor first. And, it may decide that there would be fewer reads involved in running through the table to check all three values, vs. finding the rows with the rightautoid
in the index, and then retrieving them from the table to check the other columns.FName
andLName
are in Donor, yes. They lack indices. This query is bad considering FName and LName are irrelevant given the conventions of the data and autoids must match anyway, so this would suffice to get the necessary data:select max(VisitDate) as LASTDATE FROM Visit WHERE autoid = '1234';
EXPLAIN
output into the question itself, please.