I'm struggling with MySQL index optimization for some queries that should be simple but are taking forever. Rather than post the specific problem, I wanted to ask if there is an automated way of dealing with these.
I searched around but couldn't find anything. Surely, if query/ index optimization is just following a set of steps, then someone must have written an app to automate it for a given query... or am I not appreciating the complexities involved?
Well, I can offer a SQL indexing tutorial. Let us know if you succeed with automation ;)
Not so sure about MySQL, but there are tools for Oracle and SQL Server. They cover the trivial cases, but they tend to give a false sense of safety regarding non-trivial cases. Nor do they consider the overall workload very will, they are usually limited to suggesting indexes for particular statements.