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 …
If you use the EXPLAIN SELECT statement to see how your
subqueries are treated by MySQL, you may sometimes meet the
"unique_subquery" optimization. Here is how the manual describes it:
"unique_subquery: this type replaces ref for some
IN subqueries of the following form: value IN (SELECT
primary_key FROM single_table WHERE some_expr);
unique_subquery is just an index lookup function that replaces
the subquery completely for better efficiency".Few weeks ago,
while I was reviewing a patch fixing a bug in unique_subquery, I got a
"simplification" pulsion. I told myself that:
- unique_subquery is an optimization for a special case of simple subqueries (single inner …
The MySQL explain plan is a great tool to help developers and database administrators to improve the performance of specific queries happening against a database. It is very easy to use, but its output can be confusing, so I thought I'd show a very simple example.
Let's begin by looking at a table called recipes
mysql> desc recipes; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(400) | NO | MUL | NULL | | | description | text | YES | | NULL | | | category_id | int(11) | YES | MUL | NULL | | | chef_id | int(255) | NO | MUL | NULL | | | created | datetime | YES | | …[Read more]
The MySQL explain plan is a great tool to help developers and database administrators to improve the performance of specific queries happening against a database. It is very easy to use, but its output can be confusing, so I thought I'd show a very simple example.
Let's begin by looking at a table called recipes
mysql> desc recipes; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(400) | NO | MUL | NULL | | | description | text | YES | | NULL | | | category_id | int(11) | YES | MUL | NULL | | | chef_id | int(255) | NO | MUL | NULL | | | created | datetime | YES | | …[Read more]
Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.
I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.
Really simple, and looks just like this:
- Microseconds and MariaDB
- Dealing with Assertion failure in log/log0recv.c – !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
- MySQL Windows Users – Use Grep to Search MySQL Source Code
- …
This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...
The post When EXPLAIN estimates can go wrong! appeared first on ovais.tariq.
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.
Currently, the optimizer trace includes …
Assume you have a table where you store non-overlapping
intervals using two columns, e.g. IP ranges. IP ranges are
simple to represent using integer notation:
CREATE TABLE ip_owner ( owner_id int NOT NULL,
/* some columns */ ip_start_int bigint
NOT NULL, /* IP address converted
to integer */
ip_end_int bigint NOT
NULL, /* IP address
converted to integer */
PRIMARY KEY (owner_id), INDEX ip_range
(ip_start_int, ip_end_int) ) ENGINE=InnoDB;
And then you find yourself in a situation where you want to know
who, if anyone, owns the IP address X. This can be done using the
following query:
SELECT * FROM ip_owner WHERE ip_start_int <= X AND ip_end_int …
Explain.... It is a very simple command that I feel is one
of the most overlooked commands by new MySQL users. It is also a
very valuable command available for MySQL. I realize I am
preaching to the choir for a lot of MySQL users. However, for
everyone who uses explain, we are bound to have many who do
not. The MySQL documentation on this is great and available here and Optimizing Queries
with EXPLAIN
Developer and a dba issues will continue for years, but we
can at least start on a level playing field. When writing a
query, regardless of what it is, it is a good practice is to
start it with explain first. This can achieve a couple things for
you.
- It checks your syntax to help you avoid mistakes. …