Is there a way to find missing indexes rapidly without relying on the performance schema?

Question

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.

Answer 1

There is no safe way to discover what indexes are unused.

I watched a coworker cry after deciding they that a certain index was unused (according to various tools). Then the monthly maintenance script ran and clogged up the system for hours. It was deleting a few rows from a table with a billion rows. It depended on the deleted index. :(

The following sort of works:

1. Find all queries (Select, Insert, Update, Delete).
2. EXPLAIN each one. (This assumes you have a new enough version that has EXPLAIN for write queries.)
3. Tally up what indexes are used.

The flaw is that one query may want to use a different query plan than the one you saw it using. (I have seen as many as 6 different plans used for a slightly complex JOIN.)

Another flaw is in the difficulty in finding all the queries. (In the anecdote, they forgot about the cron job.)

A caution about using EXPLAIN. It may fully evaluate all uncorrelated subqueries, thereby taking minutes or even hours, just to get the query plan. And meanwhile tying up the system at some level.

In EXPLAIN

"All" means either

• There is no useful index, or
• The Optimizer decided not to use any of the available indexes. This usually happens when no index is selective enough to be worth using.

"Possible keys" is (roughly) the list of indexes that the Optimizer, at first glance, considered using. "Key" is the name of the index that it decided to use. "Key_len" gives a clue of how many columns of a composite index it is using. (Key_len is rather cryptic and needs a long discussion.)

More importantly to the topic, Explain fails to tell you what the optimal index would be.

Also, Explain tells you what index the query will use today with the specified constants. Changing constants (as in WHERE x=432) could change the choice of index that is best.