mySQL Indexes suggestion software

Question

Is there any software that can analyze a mySQL query, and suggest a specific index creation.

I know its best to do it by hand, but i need to something that can save some time.

Many thanks,

Answer 1

The MySQL Enterprise Monitor has a Query Analyer feature. But MEM is not free.

Percona Toolkit is a free, open-source software product that gives you most of the information to do the analysis yourself.

• pt-query-digest --explain analyzes the top queries that appear in your query log and shows you their current optimization plan.

• pt-index-usage analyzes the queries in your query log and shows you how they are using indexes (and also shows you indexes that it considered but decided not to use).

Full disclosure: I work for Percona.

Answer 2

Well, this won't automate the process, but it explains how you can figure out what the best INDEX is for a given SELECT: Index Cookbook

I agree with Bill that pt-query-digest (together with the slowlog) is an excellent way to identify the "worst" queries. (Disclosure: I do not work for Percona.)