Finding Largest Tables in MySQL 8 / MariaDB 10.2

Published: January 8, 2021

Tags:

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.

It turns out that as of MySQL version 8.0.2 and MariaDB version 10.2.4 rows is now a reserved word and cannot be used as an identifier name without being quoted.

All that’s needed to resolve the issue is to quote rows like this: `rows`.

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;

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but loves dabbling in other languages like Go and Ruby. Technical topics that interest me are monitoring, security and performance. I'm also a stickler for good documentation and clear technical writing.

During the day I lead a team of developers and solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I've also spoken at a number of events.

In my spare time I blog about tech, work on open source and participate in bug bounty programs.

If you'd like to get in contact, you can find me on Twitter and LinkedIn.