Useful MySQL Queries

This whitepaper explores not so popular queries that can help to get some useful information about MySQL database and its components.

Get database or table size. Some tasks require calculating total database size on the disk. For this purpose, use this statement:

SELECT table_schema `DB Name`,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) `DB Size in MB`
FROM information_schema.tables
GROUP BY table_schema;

Get sizes of all database tables ordering from large to small:

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = SCHEMA()
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Tables without Primary Key. Sometime it is required to get all tables having no primary key defined for optimization. Here is the statement for this purpose:

select tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_schema = SCHEMA()
      and tab.table_type = 'BASE TABLE'
      and tco.constraint_type is null
order by tab.table_name;

Foreign Keys. Find all foreign keys belong to the current database:

SELECT 
  `CONSTRAINT_NAME`,                       -- Foreign key name
  `TABLE_NAME`,                            -- Foreign key table
  `COLUMN_NAME`,                           -- Foreign key column
  `REFERENCED_TABLE_NAME`,                 -- Origin key table
  `REFERENCED_COLUMN_NAME`                 -- Origin key column
FROM
  `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`  -- Will fail if user don't have privilege
WHERE
  `TABLE_SCHEMA` = SCHEMA()                -- Detect current schema in USE 
  AND `REFERENCED_TABLE_NAME` IS NOT NULL; -- Only tables with foreign keys

Have questions? Contact us