MySQL SQL query profiling and query cost tools and techniques

Question

Hi,

Working with Microsoft SQL Server I found extremely useful SQL Server Profiler and Estimated Execution Plan (available in Management Studio) to optimize queries during development and production system monitoring.

Are there similar tools (open source or commercial) or techniques available for MySQL?

Answer 1

You can get information about how a query will execute using EXPLAIN.

For example:

EXPLAIN SELECT * FROM foo INNER JOIN bar WHERE foo.index = 1 AND bar.foo_id = foo.id

It will then tell you which indexes will be used, what order the tables will be joined in, how many rows it expects to select from each table and so on.

Answer 2

As RoBorg suggests, you can use EXPLAIN to show the details of how MySQL will execute your query. I found this article more useful, as it tells you how to interpret the results and improve your SQL.

Also helpful are these articles on query cache configuration and using ALTER TABLE to add and remove indexes.