Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 9

Displaying posts with tag: range (reset)

The range access method and why you should use EXPLAIN JSON
Employee +0 Vote Up -0Vote Down
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 * 
-> 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 >









  [Read more...]
DBT-3 Q3: 6 x performance in MySQL 5.6.10
Employee +11 Vote Up -0Vote Down
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

  [Read more...]
Index merge annoyances fixed in MySQL 5.6
Employee +6 Vote Up -0Vote Down
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,
      [Read more...]
    On queries with many values in the IN clause
    Employee +4 Vote Up -0Vote Down
    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



      [Read more...]
    Index Condition Pushdown to the rescue!
    Employee +2 Vote Up -0Vote Down
    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





      [Read more...]
    Optimizer tracing: how to configure it
    Employee +1 Vote Up -0Vote Down
    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





      [Read more...]
    Optimizer tracing: Query Execution Plan descriptions beyond EXPLAIN
    Employee +6 Vote Up -0Vote Down
    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




      [Read more...]
    The MySQL range access method explained
    Employee +4 Vote Up -0Vote Down
    The range access method uses an index to read a subset of rows that form one or multiple continuous index value intervals. The intervals are defined by the query's range predicates, which are comparisons using any of =, <=>, IN(), IS NULL, IS NOT NULL, >, <, >=, <=, BETWEEN, !=, <> or LIKE.

    Some examples:
    SELECT * FROM blog WHERE author_id IN (1, 7, 8, 10)SELECT * FROM orders WHERE value > 1000
    You know that the range access method is used when EXPLAIN shows type=range.

    Naturally, there has to be an index on the column used by the range predicate. Since indexes are ordered, MySQL will, for each interval,





      [Read more...]
    Data Warehousing Best Practices: Comparing Oracle to MySQL pt 2
    +5 Vote Up -3Vote Down

    At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

    See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more

      [Read more...]
    Showing entries 1 to 9

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    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.