Skip to content

Instantly share code, notes, and snippets.

@petskratt
Last active November 22, 2018 17:51
Show Gist options
  • Save petskratt/3ce50567220f91f56868a62e8cf32ce8 to your computer and use it in GitHub Desktop.
Save petskratt/3ce50567220f91f56868a62e8cf32ce8 to your computer and use it in GitHub Desktop.
List of all tables and indexes in db
-- created to find tables with missing indexes on a WordPress install
SET @db = 'some_database';
SELECT
tbl.table_name,
idx.indexes
FROM
information_schema.tables AS tbl
LEFT JOIN(
SELECT table_name,
GROUP_CONCAT(index_name) AS indexes
FROM
information_schema.statistics
WHERE
table_schema = @db
GROUP BY table_name
) AS idx
ON
tbl.table_name = idx.table_name
WHERE
tbl.table_schema = @db;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment