How to find missing indexes

Hi

Is there anyway in MySQL for WordPress to find out “missing” indexes.

I constantly get (in Server Monitor) long waiting queries that say “copying to temp table” or “sorting result” in the state column (Navicat) when the query is something simple like

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Where this query in theory should be running very quickly it sometimes has a very long wait/execution time and I have no idea why.

Plus I don’t know who (which page) is calling it.

Is there any way in MySQL to find out “missing indexes” like MS SQL?

This is where the System has calculated that an index on X columns on X table would increase performance by a cost of X %.

I use a system to store this info from the SQL DMV’s so that I have it saved in case of a reboot of the server. This helps me over time find out when I need to add/delete indexes.

Is there ANYTHING similar in MySQL?

It seems pretty basic to me and I know EXPLAIN shows some useful data but you need to first know the query before running it and analysing.

The Slow Query Log is also useful BUT a lot of these queries when taken out and run on their own in Navicat for example run instantly so it seems like a problem in the back-end of MySQL or WordPress that is causing locks/deadlocks etc.

So far I cannot see anyway of finding out easily if one query is locking up another in MySQL.

And yes I use MySQL Tuner and all the other tuner systems all the time which always come back fine apart from don’t sort on disk and always optimize.

As I cannot really change the core queries within WordPress to run more efficiently all I can do is add indexes to help them.

So I need a way to find these “missing” indexes and problematic queries.

Thanks

Rob