MySQL Bugs: #74928: EXPLAIN which assumes that tables are big

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:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There should be a variant of EXPLAIN which would assume that tables are big.

Let call it EXPLAIN FULL:

Example:

EXPLAIN FULL SELECT * FROM t;

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.

Run EXPLAIN.

See that it is wrong for a big table.

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