Is there a software tool, like SQL Servers "Index Tuning Wizard", that will identify and create new indexes in MySQL?
It's a good thing you're thinking about setting indexes on your database tables. This is a quite important, but often forgotten development task.
Setting correct indexes depends heavily on the queries your application (in this case: Magento) runs, so it's about how the application queries the data.
Optimizing a database and its indexes is not just up to a tool running and checking the database and table structure. If so, you would need a tool which analyzes your query log but usually, as a developer, you know best quick queries you run and need.
If you want to set correct indexes, consider the following points:
- Which queries are executed often?
- Which columns are often requested in the
- What kinds of operations do you perform:
=, >, >=, <, <=, LIKE, HAVING, BETWEEN,...
- What kind of data types do you use in the WHERE condition?
You might also use the
EXPLAIN statement (
EXPLAIN SELECT ... FROM ...) to see what information is used in your query execution plan.
Starting from there you can set indexes where you see a need.
From a Magento point of view you will need to check which queries are performed by your own modules (where you can adapt the query as well as the table easily) and which queries come from the core (where you need to alter the core tables or as well rewrite some classes to optimize the query).
A good website & starting point for understanding SQL indexes is http://use-the-index-luke.com/.