Skip to content

empty_tables: use EXPLAIN SELECT count(*) to estimate rows count #95

@macbre

Description

@macbre

information_schema sometimes returns zero rows for small tables and explain select count(*) can return non-zero rows count when the table is empty. Use both methods.

See #91

Example

mysql> explain select count(*) from 0089_empty_table;
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | 0089_empty_table | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql> select count(*) from 0089_empty_table;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

mysql> select table_name, table_rows from information_schema.tables where table_name = '0089_empty_table';
+------------------+------------+
| table_name       | table_rows |
+------------------+------------+
| 0089_empty_table |          0 |
+------------------+------------+
1 row in set (0,00 sec)

Metadata

Metadata

Assignees

No one assigned

    Labels

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions