Databases Reference
In-Depth Information
Improvements in MySQL 5.6
MySQL 5.6 will include an important enhancement to
EXPLAIN
: the ability to explain
queries such as
UPDATE
,
INSERT
, and so on. This is very helpful because although one
can convert a DML statement to a quasi-equivalent
SELECT
statement and
EXPLAIN
it,
the result will not truly reflect how the statement executes. While developing and using
tools such as Percona Toolkit's
pt-upgrade
that attempt to use that technique, we've
found several cases where the optimizer doesn't follow the code path we expected when
converting statements to
SELECT
. The ability to
EXPLAIN
a statement without transform-
ing it to a
SELECT
is thus helpful for understanding what truly happens during execution.
MySQL 5.6 will also include a variety of improvements to the query optimizer and
execution engine that allow anonymous temporary tables to be materialized as late as
possible, rather than always creating and filling them before optimizing and executing
the portions of the query that refer to them. This will allow MySQL to explain queries
with subqueries instantly, without having to actually execute the subqueries first.
Finally, MySQL 5.6 will enhance a related area of the optimizer by adding
optimizer
trace
functionality to the server. This will permit the user to view the decisions the
optimizer made, as well as the inputs (index cardinality, for example) and the reasons
for the decisions. This will be very helpful for understanding not just the execution plan
that the server chose, but also why it chose that plan.
