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

Displaying posts with tag: composite index (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...]
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...]
    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...]
    Slides of my talk on B+Tree Indexes and InnoDB
    +2 Vote Up -0Vote Down

    The slides of my talk on B+Tree Indexes and InnoDB are now available for download. This slide was presented during Percona Live London 2011. You can download the slides from here.
    There are many other interesting and informative talks that were presented during Percona Live London 2011, and I think you should definitely check them out, if you haven't. They are available here.

    The post Slides of my talk on B+Tree Indexes and InnoDB appeared first on ovais.tariq.

    Showing entries 1 to 4

    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.