Is there a good tool for MySQL that will help me optimise my queries and index settings?

Question

I use MySQL in a fairly complex web site (PHP driven).

Ideally, there would be a tool I could use that would help me test the SQL queries I am using and suggest better table indexes that will improve performance and avoid table scans.

Failing that, something that will tell me exactly what each query is up to, so I can perform the optimisation myself.

Edit: a simple guide to understanding the output from EXPLAIN ... would also be useful.

Thank you.

Answer 1

There are probably query analzers out there, but for a simple first cut at it use the mysql command line, and type "explain select * from foo where bar = 'abc'". Make sure your most common queries are using indexes, try to avoid sequential scans or sorts of big tables.

Answer 2

You should look into Maatkit, which is an open source toolkit for doing all sorts of MySQL tasks. Without more information about precisely what you're trying to tune, it's hard to tell you which tools you'd be using and how, but the documentation is excellent and it covers a lot of applications.

Answer 3

As a simplest thing, enable Slow Query Log and see what queries are slow, then try to analyze them as suggested.

Answer 4

OK, before my answer, subscribe to the MySQL Performance Blog, I've learned a lot (and I thought I already knew a lot about MySQL). Also, he's got a bitchin' tools page here.

Second off, here's some info about EXPLAIN (referenced from the High Performance MySQL book from O'Reilly):

When you run an EXPLAIN on a query, it tells you everything MySQL knows about that query in the form of reports for each table involved in the query.

Each of these reports will tell you...

• the ID of the table (in the query)
• the table's role in a larger selection (if applicable, might just say SIMPLE if it's only one table)
• the name of the table (duh)
• the join type (if applicable, defaults to const)
• a list of indexes on the table (or NULL if none), *possible_keys*
• the name of the index that MySQL decided to use, key
• the size of the key value (in bytes)
• ref shows the cols or values used to match against the key
• rows is the number of rows that MySQL thinks it needs to examine in order to satisfy the query. This should be kept as close to your calculated minimum as possible!
• ...then any extra information MySQL wishes to convey

The book is completely awesome at providing information like this, so if you haven't already, get your boss to sign off on a purchase.

Otherwise, I hope some more knowledgeable SO user can help :)

Answer 5

You can try third party DB Query tool. This one will help you with different databases like MySQL, SQL, Excel and others.

Answer 6

The tool I use for the rest of my sql tweaking (SQLyog) has a new version that includes a profiler, which is awesome! (I don't work for them - I just use their product)

http://www.webyog.com/en/screenshots_sqlyog.php