Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.2, 10.0(EOL), 10.1(EOL)
-
observed in the 10.0-mdev83 tree used for DBT3
Description
With EITS (use_stat_tables='preferably') and correct stats in mysql.table/index_stats the following statements return the InnoDB internal estimates instead of the exact numbers:
- SHOW TABLE STATUS
- SHOW INDEX FROM ...
Example results for a table with 8000 rows:
+----------+
|
| count(*) |
|
+----------+
|
| 8000 |
|
+----------+
|
+---------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+----------+
|
| test.t1 | analyze | status | OK |
|
+---------+---------+----------+----------+
|
+---------+------------+-------------+
|
| db_name | table_name | cardinality |
|
+---------+------------+-------------+
|
| test | t1 | 8000 |
|
+---------+------------+-------------+
|
+---------+------------+------------+--------------+---------------+
|
| db_name | table_name | index_name | prefix_arity | avg_frequency |
|
+---------+------------+------------+--------------+---------------+
|
| test | t1 | PRIMARY | 1 | 1.0000 |
|
| test | t1 | c1 | 1 | 1.0000 |
|
| test | t1 | c2 | 1 | 81.6327 |
|
+---------+------------+------------+--------------+---------------+
|
+----------------+
|
| c2 cardinality |
|
+----------------+
|
| 97.9999 |
|
+----------------+
|
+-------+----------+--------------+-------------+-------------+
|
| Table | Key_name | Seq_in_index | Column_name | Cardinality |
|
+-------+----------+--------------+-------------+-------------+
|
| t1 | PRIMARY | 1 | c1 | 8050 |
|
| t1 | c1 | 1 | c1 | 8050 |
|
| t1 | c2 | 1 | c2 | 187 |
|
+-------+----------+--------------+-------------+-------------+
|
+------+--------+---------+------------+------+
|
| Name | Engine | Version | Row_format | Rows |
|
+------+--------+---------+------------+------+
|
| t1 | InnoDB | 10 | Compact | 8050 |
|
+------+--------+---------+------------+------+
|