dba

This is a query to report the number of rows and the estimated size of all the tables in a MySQL database:

SELECT 
  table_name, 
  table_rows, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS mb_size
FROM information_schema.tables
WHERE table_schema = 'maindb;

Results look like:

One of the key steps for performing tuning a database is to test indexing strategies and their effect on performance. It is a series of:

1. Drop the indexes
2. Create new indexes
3. Update statistics
4. EXPLAIN the important queries.

Rinse and repeat.

Yesterday I posted about how to identify the hottest table in a PostgreSQL database. Pretty much the same functionality is available for administrators of Informix databases as well; this kind of information is found in the sysmaster database which is the database engine's own database.