Using MySQL’s Analysis Tools to Improve Query Performance

“MySQL’s Explain Extended Explained

“One nice feature added to the EXPLAIN statement in MySQL 4.1 is
the EXTENDED keyword which provides you with some helpful
additional information on query optimization. It should be used
together with SHOW WARNINGS to get information about how query
looks after transformation as well as what other notes the
optimizer may wish to tell us. This article will demonstrate how to
use Explain Extended with Show Warnings to fine tune your Select
statements as well as how to rev up performance even more using
optimizer hints and SHOW STATUS.

“What the Extended Keyword Adds

“While it may look like a regular Explain statement, behind the
scenes, MySQL engineers the SQL statement into its optimized form.
Using Show Warnings afterwards prints out the optimized Select
statement. Examining it can give us a better idea of how MySQL is
interpretting our SQL. Here’s an example:

“The following query could be used to find how many employees
show up in the bonuses table. That is to say, how many employees
have received bonuses:”

Complete Story

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends, & analysis