0

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

7
  • 1
    More information would be helpful. 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).
    – RDFozz
    Jul 2, 2018 at 22:51
  • That said: Are LName and FName columns in Donor? Is Visit.autoid a primary key, or is it included in the index on VisitDate? If the only columns from Visit 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 actual Visit table itself at all. Conversely, an index on Donor.autoid isn't enough to identify the rows needed from the Donor table - it also has to evaluate LName and FName to figure out if a row belongs....
    – RDFozz
    Jul 2, 2018 at 22:54
  • ...Depending on the sizes of Donor and Visit, 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 right autoid in the index, and then retrieving them from the table to check the other columns.
    – RDFozz
    Jul 2, 2018 at 22:59
  • This is a legacy system of poor design. Donor.autoid is a primary key, and Visit.autoid is an index (essentially a poorly defined foreign key). We've moved to a new DB server and the problem has evaporated today. I suspect this is due to stats in INFORMATION_SCHEMA as the system gets used. FName and LName 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'; Jul 3, 2018 at 16:30
  • 1
    Edit the EXPLAIN output into the question itself, please.
    – RDFozz
    Jul 3, 2018 at 16:45

1 Answer 1

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??

6
  • I wanted to reduce the amount of transcribing being done and preserve the query in its original (poor) form, hence the ambiguity in the column names. There are numerous issues with the queries throughout this legacy system. Most of them I just re-write entirely. I will include the partial table definitions - these tables have over 130 columns each (ugh). Donor.autoid is a bigint(20) and Visit.autoid is a varchar(10). As a "foreign key" - with no constraints, mind you - that's of course terrible DB design. FName and LName come from Donor. Jul 4, 2018 at 19:46
  • You might be on to something with the difference in joined column types, but it still seems like JOIN Donor ON Donor.autoid = Visit.autoid AND Donor.autoid = X should be sufficient for the optimizer to also reduce the row set early by finding only those Visit records where autoid = X. Perhaps MariaDB is just missing this optimization in this version. Jul 4, 2018 at 19:55
  • @JesseSkrivseth - It's probably more complex. int = "123" is ok because the literal string can be converted before starting the query. But 123 = varchar requires converting the string to a number at runtime -- thereby failing to use any index.
    – Rick James
    Jul 4, 2018 at 22:02
  • @JesseSkrivseth - I would do an ALTER TABLE (or maybe two) to transform the VARCHAR(10) into BIGINT. (And do some data checks, and stare at the SQLs being used, etc.)
    – Rick James
    Jul 4, 2018 at 22:05
  • Thank you. It does make sense. I feel compelled to test it with different schema designs to confirm the nuances of the data type conversion issue. As far as refactoring this database, I'm inclined to boil the ocean on this one personally, though politics prevents me from making some of the changes this DB really needs. Jul 6, 2018 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.