incorrect EXPLAIN results

[dylan wenzlau]

The problem is that MySQL 5.7 always returns "100" in the filtered column of the explain (for the query below), whereas MySQL 5.6 returns a much larger number that reflects how many rows will actually be processed/filtered.

The query:

EXPLAIN EXTENDED SELECT * FROM `table` WHERE (`field1` REGEXP '(^|\\^)792863292($|\\^.*)') ORDER BY `field2` DESC limit 20;

On mysql 5.7.11-4, the explain reads like this, not reflecting the fact that the regexp filter probably only matches a couple rows out of ~10 million:

select_type=SIMPLE, type=index, possible_keys=NULL, key=field2, key_len=9, ref=NULL, rows=20, filtered=100.00, Extra=Using Where

On mysql 5.6.24-72.2, the explain reads like this, correctly displaying the estimated number of rows that will be filtered:

select_type=SIMPLE, type=index, possible_keys=NULL, key=field2, key_len=9, ref=NULL, rows=20, filtered=42905.00, Extra=Using Where

[UPDATE] (credit to Iv Segal who dug through the source code to find this)

It actually appears to be a bug fix applied from Oracle 5.7:
https://github.com/percona/percona-server/commit/86ce784cfa959dbc9511abc42f9556cec66a7646

And the bug can be found here:
https://bugs.mysql.com/bug.php?id=34124

It appears that the fix has led to EXPLAIN now hiding some crucial information in certain scenarios.

It looks like >100% was logical in cases where a limit was applied. Having this information was useful because placing a limit will not always necessarily reduce the number of actual examined rows.

For example, taking rows * filtered / 100.0f would yield a fairly good estimate of potential rows examined.

With Oracle's change it is no longer possible to predict the number of rows that could be examined, because when limit is applied, both numbers would be based on the limit rather than actual estimate of rows examined.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3446