Partitioning is a way in which a database (MySQL
in this case) splits its actual data down into separate tables,
but still get treated as a single table by the SQL layer.

When partitioning, it’s a good idea to find a natural partition
key. You want to ensure that table lookups go to the correct
partition or group of partitions. This means that all SELECT,
UPDATE, DELETE should include that column in the WHERE clause.
Otherwise, the storage engine does a scatter-gather, and queries
ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key
along with the auto increment, i.e., PRIMARY KEY (part_id,id). If
you …

I've got several useful habits over the years of work in MySQL
Support. One of them is to start working on every problem with
search for known MySQL bugs related to the problem at hand.
I'd like to share one recent case where this habit helped me to
get a solution for customer almost instantly.

It was one of rare cases when customer opened a support request
with a very clear question and even a test case. The problem was
described very precisely, more or less as follows (with table and
column names, and data changed for this blog post, surely).

I got an interesting question about EXPLAIN and the range access
method recently. The person had a query that could be written
either with a BETWEEN predicate or an IN predicate, something
similar to this:

When MySQL gets a query, it is the job of the optimizer to find
the cheapest way to execute that query. Decisions include access
method (range access, table scan, index lookup etc), join order,
sorting strategy etc. If we simplify a bit, the optimizer first
identifies the different ways to access each table and calculate
their cost. After that, the join order is decided.

However, some access methods can only be considered after the
join order has been decided and therefore gets special treatment
in the MySQL optimizer. For join conditions, e.g. "WHERE
table1.col1 = table2.col2", index lookup can only be used
in table2 if table1 is earlier in the join sequence. Another
class of access methods is only meaningful for tables that are
first in the join order. An example is queries with ORDER BY ...
LIMIT. Prior to MySQL 5.6.10 there was a bug in MySQL that made
the optimizer choose inefficient execution plans for this query
type. …

A few customers with rather extreme needs have contacted us about
a performance issue with the range optimizer. Our solution to the
problem is to introduce a new variable in MySQL 5.6,
eq_range_index_dive_limit, which can be used to control whether
or not the range optimizer will a) do index dives, or b) use
index statistics when estimating the number of rows in the ranges
of the query. The former method gives a far more accurate
estimate while the latter costs a lot less to compute.

This is what the help text has to tell about the variable:

The optimizer will use existing index statistics instead of doing
index dives for equality ranges if the number of equality ranges
for the index is larger than or equal to [the value of variable].
If set to 0, index dives are always used."Equality range" means
predicates using operators IN() or =, and it's important to
notice that the number of such ranges is counted on a per index …

A while ago, I explained how range access in a multiple-part
index works and why MySQL can't utilize key parts beyond the
first occurrence of some often used comparison operators.
Luckily, there is a great improvement underway in MySQL 5.6 that
will remedy much of this limitation. Meet Index Condition
Pushdown.

How does ICP work?

Index Condition Pushdown is a new way for MySQL to evaluate
conditions. Instead of evaluating conditions on rows read from a
table, ICP makes it possible to evaluate conditions in the index
and thereby avoid looking at the table if the condition is
false.

Let's assume that we have a multiple-part index covering columns
(keypart_1, ..., keypart_n). Further assume that we have a
condition with a comparison operator on keypart_1 that does not
allow …

In this blog post, my colleague Jørgen Løland
described a new feature of MySQL 5.6: Optimizer Tracing. I
recommend reading his article, as it presents this new feature in
a simple, easy-to-read manner.

The Optimizer Tracing feature can help understanding what the
Optimizer is doing; it is available since milestone 5.6.3,
announced October 3rd at Oracle Open World (here is the changelog). It's good to see it mature now; I
remember that Sergey Petrunia did the first prototype back in
March 2009!

Today I will be giving some must-have tips related to
handling big traces.

First thing to know, a trace lives in main memory (internally it
is allocated on the heap or free store of …

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.

Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.