List tables with table size in MySQL

The following query will list the top 5 tables by table size in MySQL. SELECT TABLE_SCHEMA, TABLE_NAME, CEIL((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS table_size_in_MiB, TABLE_ROWS AS estimated_row_count, AVG_ROW_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN (‘information_schema’, ‘innodb’, ‘mysql’, ‘performance_schema’, ‘phpmyadmin’, ‘sys’, ‘tmp’) ORDER BY table_size_in_MiB DESC LIMIT 5; Note that TABLE_ROWS is only accurate …

Insert record only if it does not exist in table without unique index

Reference: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table/#3164741 Supposing we have a database table named distributor and we wish to insert a record only if there is no existing record with the same name and country code. The table only has a PRIMARY key but no UNIQUE index on (name, country_code) – think “legacy” and “no privileges to modify database” 😛 …