Windowing Functions can get quite complex very quickly when you
start taking advantage of the frame clause. Ranges and rows can
get confusing. So for review lets look at how the
specification looks:
Window_spec:
[window name] [partition clause] [order clause]
[frame clause]
That looks simple. And them come terms like UNBOUNDED PRECEDING
that could put a knot in your gut. The manual is not
exactly written to help novices in this area get up to
speed. But don't panic. If you work through the
examples that follow (and please do the preceding part of this
series before trying these examples) you will have a better
appreciation of what is going on with window function.
The Frame Clause
So the frame clause is optional in the window function. A
frame is considered a subset of the current partition and defines
that subset. Frames are determined with …
In this blog, we’ll quickly look at MySQL partitioning.
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 …
[Read more]
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:
mysql> EXPLAIN SELECT *[Read more]
-> FROM orders WHERE customer_id BETWEEN 7 AND 10 AND value > 500;
+----+-------------+--------+-------+----------+----------+------+------
| id | select_type | table | type | key | key_len | rows | Extra
+----+-------------+--------+-------+----------+----------+------+------
| 1 | SIMPLE | orders | range | cust_val | 10 | 91 | ...
+----+-------------+--------+-------+----------+----------+------+------
mysql> EXPLAIN SELECT *
-> FROM orders WHERE customer_id IN (7,8,9,10) AND value > 500;
+----+-------------+--------+-------+----------+----------+------+------
| id | select_type | table | type | key | key_len | rows …
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. …
While the index merge access types certainly are useful
for a number of queries, there has been some frustration
expressed both from customers and the community about how
it...
- is not used when it should have been
- is used when ref access is obviously better
- merges suboptimal indexes
- is too restricted in which conditions can be used
I could come up with numerous examples of related bugs and feature requests dating back more than six years. To list a few: 17673, 30151, 23322, 65274, …
[Read more]
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.
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 …