Tuesday, December 2, 2008

MySQL Data and Index Sizes

Here is a very useful mysql query that I learned today:

DATA_LENGTH / 1024 / 1024 / 1024 AS DATA_LENGTH,
INDEX_LENGTH / 1024 / 1024 / 1024 AS INDEX_LENGTH
FROM information_schema.TABLES
WHERE table_schema = 'SCHEMA NAME';

Replace the 'SCHEMA NAME' constant with the name of your database schema, and it will tell you the current size of your tables and the indexes on those tables.

The table we were researching had half a billion rows, with 31 gigs of data, and 63 gigs of indexes.