Finding Largest Tables in MySQL 8 / MariaDB 10.2
Published: January 8, 2021
If you’re like me the Percona blog’s “Finding the largest tables on MySQL Server” from 2008 is a resource you frequently visit.
However, when running the query recently I experienced the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rows,
Playing around a little bit I determined that the issue was caused by the fact that the query in the Percona article uses
rows as an identifier name without quoting.
All that’s needed to resolve the issue is to quote rows like this:
Here’s the updated full query that’s compatible with MySQL 8 / MariaDB 10.2:
SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') `rows`, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10;