I have recently discovered a feature in MySQL which is useful in debugging performance issues with queries.

We all know about the EXPLAIN statement which looks something like

EXPLAIN SELECT statement

This is used to display information from the optimizer about the query execution plan for the SELECT statement including information about how tables are joined and in which order. There is also a second statement called EXPLAIN EXTENDED which will provide even more information about what is happening under the hood.

To show you how this works lets take a common query from the current development version of osCommerce 3.x

EXPLAIN select distinct p.products_id from osc_products p left join osc_product_attributes pa on (p.products_id = pa.products_id) left join osc_templates_boxes tb on (pa.id = tb.id and tb.code = “Manufacturers”), osc_products_description pd, osc_categories c, osc_products_to_categories p2c where p.products_status = 1 and p.products_id = pd.products_id and pd.language_id = 1 and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id

You should get some output similar to the following

As you can see there is lots of useful information there to help understand just what is happening. But it seems the MySQL engine has some other things it would like to tell us. And we get to this extra information by adding the keyword EXTENDED in combination with SHOW WARNINGS to get information about how query looks after transformation as well as any other notes the optimizer may wish to tell us.

Here is the same query again but with some extra information.

You can see there is a new column here called “filtered” which was added in MySQL 5.1.12. This column indicates an estimated percentage of table rows that will be filtered by the table condition.

Now if we send the statement

SHOW WARNINGS \G

We get back exactly how the optimizer has changed the query internally before executing it

select distinct `osc3`.`p`.`products_id` AS `products_id` from `osc3`.`osc_products` `p` left join `osc3`.`osc_product_attributes` `pa` on(((`osc3`.`p`.`products_id` = `osc3`.`pd`.`products_id`) and (`osc3`.`p2c`.`products_id` = `osc3`.`pd`.`products_id`) and (`osc3`.`pa`.`products_id` = `osc3`.`pd`.`products_id`))) left join `osc3`.`osc_templates_boxes` `tb` on(((`osc3`.`tb`.`code` = 'Manufacturers') and (`osc3`.`tb`.`id` = `osc3`.`pa`.`id`))) join `osc3`.`osc_products_description` `pd` join `osc3`.`osc_categories` `c` join `osc3`.`osc_products_to_categories` `p2c` where ((`osc3`.`c`.`categories_id` = `osc3`.`p2c`.`categories_id`) and (`osc3`.`pd`.`language_id` = 1) and (`osc3`.`p`.`products_id` = `osc3`.`pd`.`products_id`) and (`osc3`.`p2c`.`products_id` = `osc3`.`pd`.`products_id`) and (`osc3`.`p`.`products_status` = 1))

As you can see to get better performance the engine has changed the order of the query. Using this knowledge it is possible for the developer to understand exactly what the optimizer is doing and possibly find a better way to achieve the same results.

Hopefully I am not the only person in the world who didn’t know about this EXTENDED option ;–)