I’ve always believed that the MySQL Information_schema.tables.table_rows figure for Innodb tables to be while approximate, approximately accurate.
Today I found that the figures varied on one table from 10x to 100x wrong.
Before performing an ALTER I always verify sizes for reference.
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | table_s | table_name | engine | row_format | table_rows | avg_row_length | total_mb | data_mb | index_mb | today | +---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | XXXXX | s_a | InnoDB | Compact | 208993 | 7475 | 1491.5312 | 1490.0156 | 1.5156 | 2009-09-09 |
mysql> alter table s_a modify col VARCHAR(255);
Query OK, 23471 rows affected (4 min 26.23 sec)
After
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | XXXXX | s_a | InnoDB | Compact | 2407063 | 629 | 1447.0312 | 1445.5156 | 1.5156 | 2009-09-09 |
I was so caught out by this. The table reported 200k rows, but the alter returned 23k, that’s like 10x out.
I ran my query again, and the second time I got.
+---------+------------+--------+------------+------------+----------------+------------+------------+-----------+------------+ | XXXXX | s_a | InnoDB | Compact | 21813 | 69487 | 1447.0312 | 1445.5156 | 1.5156 | 2009-09-09 |
This is closer to what I’d expected, 22k verses 23k.
I have to say, while I have always treated the data and index size as accurate, I now how little confidence in the table_rows any more.