Understanding why MySQL chooses a particular join order or why table scan is chosen instead of range scan is often very hard even for experienced MySQL users. Two almost identical queries, differing only in constant values, may produce completely different plans. That's why we're introducing a great new feature in 5.6: Optimizer Tracing. The target users of this feature are developers and MySQL users experienced enough to understand the ins and outs of EXPLAIN.

What Optimizer Tracing is
You may already have guessed this, but optimizer tracing is a printout  of important decisions the MySQL optimizer has done during the process of making the Query Execution Plan.

The trace is presented in JSON format which is easy to read both for humans and others.

Currently, the optimizer trace includes …

Welcome to Profiling InfiniDB Multi-Join, part of a series designed to highlight different aspects of the InfiniDB join capabilities. InfiniDB is engineered and optimized to execute join operations for millions or billions of rows using a number of key features:

Hash Joins.
Parallel processing (multi-threaded and optionally distributed).
Join sequencing to minimize data shipping costs. (Right-deep join tree instead of left-deep).
InfiniDB multi-join (stream 1 large table past up to 60 hRead More...

Welcome to Profiling InfiniDB Multi-Join, part of a series designed to highlight different aspects of the InfiniDB join capabilities. InfiniDB is engineered and optimized to execute join operations for millions or billions of rows using a number of key features:

Hash Joins.
Parallel processing (multi-threaded and optionally distributed).
Join sequencing to minimize data shipping costs. (Right-deep join tree instead of left-deep).
InfiniDB multi-join (stream 1 large table past up to 60 hRead More...

Welcome to Profiling InfiniDB Multi-Join, part of a series designed to highlight different aspects of the InfiniDB join capabilities. InfiniDB is engineered and optimized to execute join operations for millions or billions of rows using a number of key features:

Hash Joins.
Parallel processing (multi-threaded and optionally distributed).
Join sequencing to minimize data shipping costs. (Right-deep join tree instead of left-deep).
InfiniDB multi-join (stream 1 large table past up to 60 hRead More...

In a previous post, I mentioned the availability of the dtrace extension for Cool Stack's PHP.  Using this extension and the Cool Stack MySQL, it is possible to analyze the performance of your application running on this stack. At JavaOne, we demoed this using the open source MediaWiki and SugarCRM applications.  dtrace is especially useful in analyzing complex multi-tier applications like AMP. Thanks to Angelo Rajadurai for the creation of the scripts that I describe below.

Analyzing PHP calls

So, let's look at a simple dtrace script that counts how many times a particular PHP function is called :

#!/usr/sbin/dtrace -Zqs

php*:::function-entry
/arg0/
{
        @[copyinstr(arg0)]=count();
}

You can copy the …

DB User = dba
DB Pass = dba
DB Name = TEST
command = trace_on
argument = TRACE_INSERT

Voila:

$ dbmcli -u dba,dba -d TEST trace_on TRACE_INSERT