Bug #74928 EXPLAIN which assumes that tables are big
Submitted: 19 Nov 2014 14:30 Modified: 19 Nov 2014 14:36
Reporter: Victor Porton Email Updates:
Status: Open Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 2014 14:30] Victor Porton
There should be a variant of EXPLAIN which would assume that tables are big.

Let call it EXPLAIN FULL:



For index cardinality we may assume that the tables have exactly (for example) 10000 rows and each index cardinality is 10000.

The purpose of this feature is that a developer can test usage of indexes and other features without first preparing a table filled with data, but prepare just a TABLE CREATE statement.

A rationale for this that to prepare a filled table a developer may need to write a complex script (say in Perl or PHP). So to test MySQL one needs to switch to other programming languages. I believe that explaining MySQL should be self-contained.

How to repeat:
Create an empty table.


See that it is wrong for a big table.
[19 Nov 2014 14:36] Victor Porton
We can assume that for columns whose data type ranges highly are restrained (such as ENUM or TINYINT columns) index contains less values.

For example, the cardinality for a column of type ENUM('male','female') NOT NULL would be 3 ('', 'male', 'female').