SELECT
t.TABLE_SCHEMA
,t.TABLE_NAME
,c.COLUMN_NAME
,IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS Indexed
FROM information_schema.TABLES as t
INNER JOIN information_schema.COLUMNS as c
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.COLUMN_NAME LIKE '%_id'
LEFT JOIN information_schema.KEY_COLUMN_USAGE as kcu
ON kcu.TABLE_SCHEMA = t.TABLE_SCHEMA
AND kcu.TABLE_NAME = t.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
AND kcu.ORDINAL_POSITION = 1
WHERE kcu.TABLE_SCHEMA IS NULL
AND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');
I executed the above and found that post_id inside wp_postmeta was missing an index.
EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND (
( wp_postmeta.meta_key = 'group' AND wp_postmeta.meta_value = '1394383' )
) AND wp_posts.post_type = 'stores' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
Performed the query above to see if there was a missing index, because I used the performance schema to see that wp_postmeta had a missing index, but I got the following:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "wp_postmeta" "ref" "post_id,meta_key,post_id meta_key,hg_mkey_mintval,hg_pid_mkey" "post_id meta_key" "1170" "const,const" "1" "Using where; Using temporary; Using filesort"
"1" "SIMPLE" "wp_posts" "eq_ref" "PRIMARY,type_status_date" "PRIMARY" "8" "pr_main.wp_postmeta.post_id" "1" "Using where"
https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
I've read that if the type is "all" then an index is needed, but after checking the explain query, I noticed it doesn't seem to suggest that there's an index missing. So I did:
SHOW INDEX FROM wp_postmeta;
And the index post_id was there.
Is there a way to find missing indexes rapidly without relying on the performance schema, because it seems that it was installed and then someone remove it, thus explaining why the tables inside performance schema weren't up to date.