Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 53 Next 23 Older Entries

Displaying posts with tag: explain (reset)

Priority queue optimization for filesort is now visible in MariaDB 10.0
+1 Vote Up -0Vote Down

TL;DR: Priority queue optimization for filesort with small LIMIT is now visible in MariaDB: there is a status variable and you can also see it in the slow query log (KB page link).

A longer variant:
One of the new optimizations in MySQL 5.6 is ability to use a priority queue instead of sorting for ORDER BY … LIMIT queries. The optimization was ported into MariaDB 10.0 long ago, but we still get questions if/when it will be ported. I guess, the reason for this is that, besides the query speed, you can’t see this optimization. Neither EXPLAIN, nor EXPLAIN FORMAT=JSON or PERFORMANCE_SCHEMA or status variables give any indication whether filesort used priority queue or the regular quicksort+merge algorithm.

MySQL 5.6 has

  [Read more...]
New feature in MariaDB 10.1: ANALYZE statement
+5 Vote Up -1Vote Down

Last week, yours truly has pushed a new feature into MariaDB 10.1 tree: ANALYZE statement.

The idea of this feature is to make it easy to compare query plan with query execution. ANALYZE statement will run the statement, and produce EXPLAIN-like output, where optimizer’s estimates are followed by numbers that were observed when running the query. The output looks like this:


  • Next to rows there is r_rows column which shows how many records were read from the table.
  • Next to filtered there is r_filtered column which shows which fraction of records was left after the part of the WHERE condition attached to the table was checked.

I think this should explain the feature. If

  [Read more...]
Engineering behind EXPLAIN FORMAT=JSON (or lack thereof)
+3 Vote Up -3Vote Down

MySQL 5.6 has added support for EXPLAIN FORMAT=JSON. The basic use case for that feature is that one can look at the JSON output and see more details about the query plan. More advanced/specific use cases are difficult, though. The problem is, you can’t predict what EXPLAIN FORMAT=JSON will produce. There is no documentation or any kind of convention regarding the contents of JSON document that you will get.

To make sure I’m not missing something, I looked at MySQL Workbench. MySQL Workbench has a feature called Visual Explain. If you want to use, prepare to seeing this a lot:

In Workbench 6.1.4 you get it for (almost?) any query with subquery. In Workbench 6.1.6 (released last week), some subqueries work, but it’s still easy to hit a

  [Read more...]
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:
-> 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 | ...

-> FROM orders WHERE customer_id IN (7,8,9,10) AND value >

  [Read more...]
InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+1 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table - 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)

The query is:


This is a straight-forward query with no WHERE clause.

read more

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+0 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)

The query is:


This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
  [Read more...]
Many-table joins in MySQL 5.6
+3 Vote Up -0Vote Down

I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.

Isolating the problem

As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was that EXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW

  [Read more...]
Unittesting your indexes
+1 Vote Up -0Vote Down
During FOSDEM PGDay I watched the "Indexes: The neglected performance all-rounder" talk by Markus Winand. Both his talk and the "SQL Performance Explained" book (which is also available online) are great.

The conclusion of the talk is that we should put more effort in carefully designing indexes. But how can we make sure the indexes are really used now and in the future? We need to write some tests for it.

So I wrote a small Python script to test index usage per query. This uses the JSON explain format available in MySQL 5.6. It's just a proof-of-concept so don't expect too much of it yet (but please sent pull requests!).

A short example:

import indextest

  [Read more...]
+2 Vote Up -3Vote Down

MariaDB 10.0 has SHOW EXPLAIN feature. It was coded by yours truly and first introduced about a year ago. Last release of MySQL 5.7 introduced EXPLAIN FOR CONNECTION, which looks rather similar to MariaDB’s SHOW EXPLAIN. I was wondering how these two compare.

The basics

The usage scenarios are similar. In both cases, you first need a connection id of a running query. It is typically obtained by running SHOW PROCESSLIST:

MariaDB [(none)]> show processlist;
| Id | User | Host      | db       | Command | Time | State        | Info                        | Progress |
  [Read more...]
Discover MyXplain, another way to learn, search, find and stroll
+4 Vote Up -1Vote Down



I’m very glad to announce you that MyXplain is released today.

This project is the result of many weeks of work for my friend Max and I and we are obviously very proud to present it.

Please take a time to browse it and find all the surprises that awaiting you.

We wanted to make an unique place, a gathering place where you can find all you want to know about the most useful MySQL commands.
We started with the explain command because we consider it as one of the most important.
show processlist and show slave status will be the next proposed commands on

  [Read more...]
Covering or not covering, that is the question
+2 Vote Up -2Vote Down

I’m currently working on a new project about the explain command output.
For my research I’ve read a lot of documentations, presentations and blog posts and I would like to focus on the join type in the explain output.
And in particular when type=index in this explain output.

Take a look at what says the official documentation about type=index :

Wait a second, only the index tree is scanned? really?
This description seems to be the definition of a covering index.
But a covering index is only characterized by a “Using index” in

  [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...]
    Recent Presentations Buenos Aires MySQL/NoSQL/Cloud Conference
    +2 Vote Up -0Vote Down

    The first annual Latin America MySQL/NoSQL/Cloud Conference was held in Buenos Aires Argentina from June 26-28. Kudos to Santiago Lertora from Binlogic who had the vision for the conference in his country and made it happen. I look forward to the second annual event.

    My first presentation was “Improving Performance with Better Indexes”. This presentation details the six steps to SQL performance analysis, Capture, Identify, Confirm, Analyze, Optimize and Verify. An explanation of MySQL EXPLAIN, and working examples to create indexes and better covering indexes in several examples are provided. A production example of a 13 table join is used to detail how covering indexes and partial column indexes can make a dramatic improvement in performance.

      [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...]
    Understanding the unique_subquery optimization
    Employee +3 Vote Up -0Vote Down
    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

      [Read more...]
    Explaining MySQL's EXPLAIN
    +0 Vote Up -0Vote Down

    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)
      [Read more...]
    Added a Table of Contents
    +1 Vote Up -0Vote Down

    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:

      [Read more...]
    When EXPLAIN estimates can go wrong!
    +0 Vote Up -0Vote Down

    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.

    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...]
    +2 Vote Up -0Vote Down

    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.

      [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...]
    The meaning of ref=func in MySQL EXPLAIN
    Employee +3 Vote Up -0Vote Down
    When EXPLAIN shows that a table is accessed using the [eq_]ref access type, we're used to look at the ref column to see where MySQL gets the value to look up from. Usually, we see either "const" if the value is provided as a constant in the query or a column name if the value is read from a column in an already read table:

    SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber
    FROM accounts_receivable as acc_rec
    WHERE acc_rec.cust_id=1;
    | id | select_type | table | type | key | ref |
    | 1 | SIMPLE | acc_rec | ref | cust_id | const |

    SELECT acc_rec.cust_id, acc_rec.amount, acc_rec.invoicenumber,

      [Read more...]
    On Covering Indexes and Their Impact on Performance
    +0 Vote Up -0Vote Down
    The purpose of this post is to describe what covering indexes are and how they can be used to improve the performance of queries. People mostly use indexes to filter or sort the results but not much thought is given to actually reduce the disk reads by using proper indexes. So I will show you how to reduce disk reads and hence improve the performance of queries by utilizing indexes properly.
    Utilizing multiple indexes per MySQL table join
    +1 Vote Up -1Vote Down

    Historically it was considered that MySQL will generally use only one index per referenced table in a SQL query. In MySQL 5.0 the introduction of merge indexes enabled for certain conditions the possibility to utilize two indexes however this could result in worst performance then creating a better index. In MySQL 5.1 it became possible to control optimization switches with the optimizer_switch system variable.

    However in explaining how to utilize the intersection, union and sort union in queries I discovered that MySQL could use three indexes for one given table.

            Extra: Using union(name,intersect(founded,type)); Using where

    I was not aware of this.

    Extra: Using Index
    +1 Vote Up -1Vote Down

    Many people consider this information in the MySQL Query Execution Plan (QEP) to indicate that the referenced table is using an index. It actually means that ONLY the index is used. This can for larger and more frequent queries provide a significant boost.

    In a recent example, adding an index dropped a query from 190ms to 6ms. However adding a better index dropped that 6ms query to 1.2ms. When executed 100s/1000s of times per second these millisecond improvements can have a huge benefit in greater scalability. While people often tune slow running queries, in a well tuned system shaving milliseconds of queries, in this example making 6ms query 80% better is a far greater improvement.

    You can get a detailed explanation of how to identify, create and verify covering indexes from my Percona Live presentation

      [Read more...]
    Optimizing UPDATE and DELETE statements
    +3 Vote Up -0Vote Down

    While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

    To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

    UPDATE t
    SET	c1 = ‘x’, c2 = ‘y’, c3 = 100
    WHERE c1 = ‘x’
    AND	d = CURDATE()

    You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

      [Read more...]
    mk-visual-explain … visual
    +4 Vote Up -0Vote Down

    I love cool toys, don’t you? Especially when they help me optimize ugly, nasty, evil looking queries :)
    I use mk-visual explain to help me understand better what the h*ll is happening on my MySQL servers and how they are being butchered, but today I discovered a small yet neat tool, which I think might have been overlooked. It uses mk-visual-explain, but does it visually, enabling you to open and close sections of the explain tree structure. I’m talking about http://explain.plosquare.com/.

    Thank you Jan Ploski

    Because Sharing is Caring

    Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL
    +16 Vote Up -0Vote Down

    When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.

    The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.

    For instance, here is a common query which could be inefficient:

    SELECT id FROM t WHERE id='1';

    And here is the CREATE TABLE output:

    mysql> show create table tG
      [Read more...]
    MySQL’s SQL Deviations and Extensions
    +3 Vote Up -1Vote Down

    Today at Kaleidoscope I will be doing a 90-minute session comparing MySQL’s SQL syntax to the ANSI/ISO SQL:2003 standard, entitled What Do You Mean, “SQL Syntax Error”?

    You can download the PDF slides now.

    For those that may be following along the presentation later today (4 pm Eastern time), here are some links that I may throw out during the session:

    • SQL 2003 standard – actually it is “Information taken from the Final Committee Draft (FCD) of ISO/IEC 9075-2:2003″ but it’s extremely close to the actual standard. The actual standard is a document that costs a non-trivial amount of money to get, and cannot be

      [Read more...]
    Showing entries 1 to 30 of 53 Next 23 Older Entries

    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.