MySql execution path suddenly varies a lot, is inconsistent and slow

Question

I’m having trouble with the execution path on MySQL, leading to slow and inconsistent queries. This is a brand new phenomenon. We have other tables with the same exact (well, as close as you can get) set-up which are fine, but for some reason, creating new tables now has this slow/inconsistent problem.

We are using version: "mysql Ver 14.14 Distrib 5.6.31, for debian-linux-gnu" with InnoDB. The database lives in a vagrant box.

The behaviour was reproduced at another computer, and after brand new versions of the vagrant box.

As I said, the db is in a vagrant box on my local machine, and my machine is not under heavy load.

t1 has around 1m rows. t2 is a new table.

This is the simplest query that consistently reproduces the problem:

SELECT
    *
FROM
    redacted_t1 AS t1
        JOIN
    redacted_t2 AS t2 ON t1.a_column = t2.id
WHERE
t2.c_column != 'asdff'
ORDER BY t1.b_column DESC;

See below some examples of execution paths that are slow (over 3 s)

I have seen at least 2 other execution paths, (that were also slow) but since it is hard to reproduce (random?) I cannot post them here.

Sometimes, but not often, I don’t know how or why, the following execution path happens:

This is very fast, 0.00 s. Sometimes having a brand new version (as in a new vagrant box) of the database, and running optimize on t1 and t2 produces this result. Sometimes the optimize does nothing. Sometimes this execution state is achieved without optimize table. Notice the much lower the count is for 'rows' for t1 compared to the slow execution paths. This is consistent with what I see if I run "SHOW STATUS;".

CREATE TABLE `redacted_t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

  -- redacted

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

CREATE TABLE `redacted_t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_column` int(11) DEFAULT NULL,

  -- redacted

  PRIMARY KEY (`id`),

  -- redacted

  KEY `redacted_t1_a_column` (`a_column`),

  -- redacted

  CONSTRAINT `fk_redacted_t1_2032420404` FOREIGN KEY (`a_column`) REFERENCES `redacted_t2` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=redacted DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

So I have a couple of questions:

1) Why is the execution path so inconsistent, and why have we never encountered this before?

2) How do we go about fixing this, so the query that should take 0.00 s does not randomly take 3 s?

Answer 1

Solved.

Apparently, the optimizer is... not that great. If the optimizer cannot handle your query, make the query slightly more complicated.

So I added a column to the ORDER BY. This fixes everything. Not ideal, but for some reason it works.

SELECT
    *
FROM
    redacted_t1 AS t1
        JOIN
    redacted_t2 AS t2 ON t1.a_column = t2.id
WHERE
t2.c_column != 'asdff'
ORDER BY t1.b_column, t2.id DESC;

Answer 2

You can try running EXPLAIN EXTENDED, followed by SHOW WARNINGS, to get more details about the query execution plan. See 8.8.3 Extended EXPLAIN Output Format for details.

You can also try running ANALYZE TABLE on t1 and t2 to ensure MySQL is using updated table statistics when choosing its execution plan.

Adding an index on redacted_t2.c_column might help, since you're filtering on that column.

From the EXPLAIN output, it seems MySQL sometimes isn't using the index redacted_t1_a_column. You can encourage or force the database to use the index with index hints, e.g. USE INDEX or FORCE INDEX.

Answer 3

SET innodb_stats_sample_pages = 30;
ANALYZE TABLE t1;
ANALYZE TABLE t2;

Then see if it is more consistent. Since you are running >5.6.6, the stats should be 'persistent'. Don't use OPTIMIZE TABLE.

Moving on, to optimizing:

Do you really need all the columns from both tables (SELECT *)? It makes a difference in optimizations and indexes. Have you shown us all the relevant indexes? Are there any TEXT or BLOB columns? Do you need to fetch them?

What percent of the table is t2.c_column != 'asdff'? If it is a small percent, then you need INDEX(c_column).

Is t2 only 5 rows long? If so, indexes, explain plans, etc, won't matter much.