MySQL Query and Query Processing Tools and Techniques MySQL
Working with Microsoft SQL Server I've found an extremely useful SQL Server Profiler and Estimated Execution Plan (available in Management Studio) for optimizing queries while monitoring development and production systems.
Are there any similar tools (open source or commercial) or methods available for MySQL?
source to share
You can get information on how the request will be executed 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 will be merged into tables, how many rows it will select from each table, etc.
source to share
As RoBorg suggests, you can use EXPLAIN to show the details of how MySQL will execute your query. I found this article more helpful as it tells you how to interpret the results and improve your SQL.
Also helpful are these articles in query cache configuration and using ALTER TABLE to add and remove indexes.
source to share
You can take a look at http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Last_query_cost . This will help you know if the query is better or not, in terms of CPU cycles or table scans.
SHOW STATUS LIKE 'Last_query_cost';
source to share