Strange query optimization issue - logically the same, different performance

Question

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'.

Answer 1

You really need to provide SHOW CREATE TABLE. A guess is that autoid is a different type in the two tables. A strong clue is the 8 (BIGINT?) and the 12 (some kind of VARCHAR or DECIMAL?).

Redundant: Donor.autoid = Visit.autoid is in both the ON (where it belongs) and the WHERE (where it is harmlessly redundant).

When JOINing, please qualify all columns -- which table are FName, VisitDate, etc, in??