CODEWITHSUNDEEP
mysqlindexingquery-optimization
alan
alan

Reputation: 4447

MySQL Index Optimization

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?

Upvotes: 2

Views: 2511

Answers (3)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26739

There are tools, that can, with the query log given, show you which indexes are not used, and you can, by enabling logging of queries, that do not use index, see which one need an index. The problem is that indexes are expensive and you cannot just index everything, and which indexes you need depends on your queries.

Upvotes: 0

Markus Winand
Markus Winand

Reputation: 8746

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.

Upvotes: 1

Mchl
Mchl

Reputation: 62395

IF it was that simple, you'd have automated index builder within MySQL.

Actually there is a query optimizer build into MySQL and it transparently rewrites your queries into what it finds most optimal form before executing them. It doesn't always work all that well though, and has it's own quirks. Knowing these helps avoid some common pitfalls (like using dependent subqueries)

Upvotes: 0

Related Questions