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 70 Next 30 Older Entries

Displaying posts with tag: optimizer (reset)

The MySQL Optimizer Cost Model Project
Employee_Team +3 Vote Up -0Vote Down

You may not be aware of this but the foundation that the MySQL optimizer builds on when choosing a query plan – the cost model – is for the most part very old. At least in tech terms.

Much of it was written in another millennium, at a time when “Forest Gump” and “Titanic” won Oscars and “Baywatch” was the big thing on TV. Although the revision history doesn’t go that far back, it wouldn’t come as a surprise if it predates that annoying “Macarena” song and even “The Sign” (Ace Of Base) – don’t follow those links unless you’re feeling very brave…

Thankfully, a lot has happened since Ace of

  [Read more...]
A new dimension to MySQL query optimizations – part 2
Employee_Team +0 Vote Up -0Vote Down

This is “A new dimension to MySQL query optimizations – part 2″. If you didn’t read part 1 already I suggest you skim through it before reading on.

To summarize, the problem at hand is this: Given a query with a join between two or more tables, the MySQL optimizer’s mission is to find the best combination of join order and access method so that the response time becomes as low as possible. The optimizer does this by calculating the cost of each combination and then picks the cheapest one.

Consider the following query:

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01"

The

  [Read more...]
A new dimension to MySQL query optimizations – part 1
Employee_Team +4 Vote Up -0Vote Down

It’s not radical to claim that one of the most important tasks of any DBMS query optimizer is to find the best join order for the tables participating in a query. Is it, e.g., better to read country or city first in

SELECT * 
FROM country JOIN city ON country.id=city.countryid 
WHERE city.population > 1000000 AND 
      country.region="EMEA"

employee or department first in

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01" AND
      department.location="Paris"

If the optimizer gets this wrong, the resulting response time may be disastrous (or hilarious, depending on your sense of humour).

Simply put (and ignoring some edge cases), the MySQL optimizer does the following to find the cheapest

  [Read more...]
Fun with Bugs #31 - what's new in MySQL 5.6.17
+1 Vote Up -0Vote Down
MySQL 5.6.17 will probably be announced loudly at or immediately before Percona Live MySQL Conference & Expo next week. But official release announcement via email was made on March 28, release notes and binaries to download are already available, so why not to check them carefully to find out what to expect from this 8th minor release of MySQL 5.6 GA...

First of all, it seems Oracle still does not hesitate to introduce new features and behavior in the process. Just check these major changes:
  • Starting with 5.6.17, MySQL now supports rebuilding regular and partitioned InnoDB tables using



  [Read more...]
You asked for it: new default for eq_range_index_dive_limit
Employee_Team +3 Vote Up -0Vote Down

In MySQL 5.6 we made it possible to estimate the number of fetched rows for queries with many equality predicates (e.g., many values in the IN clause). The new way of estimating fetched rows kicks in if the number of equality predicates exceeds the value of eq_range_index_dive_limit, which has a default of 10.

The good thing about the new way of estimating number of rows is that it is faster than the old way (index dives). On the other hand it is also less accurate.

As it happens, we got push back from the community that the default value should have been higher. Among others we heard

  [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:
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...]
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...]
Filesort optimization in 5.7.3: pack values in the sort buffer
Employee_Team +4 Vote Up -0Vote Down

In MySQL, filesort is the catch-all algorithm for producing sorted results for ORDER-BY or GROUP-BY queries. MySQL has two algorithms for filesort, both the original and the modified algorithms are described in the user manual.  The most commonly used algorithm is the so called modified algorithm, it is used for all cases except when BLOB and TEXT column are involved.

In 5.7.3, Tor Didriksen in the optimizer team introduced one more optimization that applies to the modified algorithm.  Let us first take a look at how MySQL´s modified filesort algorithm worked up to 5.7.2.

  • Read the rows that match the WHERE clause.
  • For each row, record a tuple of values consisting of the sort key value and the additional fields referenced by the query.
  • When
  •   [Read more...]
    Fun with Bugs #29 - important bug fixes in MySQL 5.6.16
    +3 Vote Up -0Vote Down
    As you should already know, Oracle had released MySQL 5.6.16 officially on January 31, 2014. You can find all the details in official release notes. In this post I decided to concentrate on important fixes for bugs reported by community in 4 most important categories: InnoDB, replication, partitioning and optimizer.

    Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself

      [Read more...]
    State of the UNION
    Employee_Team +7 Vote Up -0Vote Down

    Since union queries were introduced in MySQL 4.0.0, they have been processed by executing each part of the union and adding the result to a temporary table. Then a final query block is executed that, if needed, filters out duplicates, do global ordering and limit the number of output rows. The good thing about this method is that it works for all union queries. The bad thing is that it always uses a temporary table, even in the really simple cases, and there’s always an extra query block to execute, even if there’s no sorting and no duplicate filtering.

    As of MySQL 5.7.3, UNION ALL doesn’t use temporary tables unless needed for sorting. The result of each part of the union will be sent directly back to the client, without waiting in a temporary table or passing through an extra query block. When 5.7.3 came out, Facebook (who filed the 

      [Read more...]
    Multiple column index vs multiple indexes with MySQL 5.6
    +4 Vote Up -0Vote Down

    A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution. But with all the recent optimizer improvements, is there anything different with MySQL 5.6?

    Setup

    For this test, we will use these 2 tables (same structure as in Peter’s post):

    CREATE TABLE t1000merge (
      id int not null auto_increment primary key,
      i int(11) NOT NULL,
      j int(11) NOT NULL,
      val char(10) NOT NULL,
      KEY i (i),
      KEY j (j)
    ) ENGINE=InnoDB;
    CREATE TABLE t1000idx2 (
      id int not null auto_increment primary key,
      i int(11) NOT
      [Read more...]
    Fun with Bugs #28 - regression bugs in MySQL 5.6
    +0 Vote Up -0Vote Down
    2013 was a great year for MySQL Community. New MySQL 5.6 GA release with its increased throughput, scalability and new features as well as more interaction and cooperation with MySQL Community from Oracle side brought us a lot of new perspectives and good feelings over the year.

    Unfortunately new MySQL 5.6 GA release also reminded about old and well known problem with new MySQL versions. They all introduce new regression bugs. MySQL 5.6 had not become an exception.

    Note that according to good old tradition (that I hope will be followed in 2014) bugs that demonstrate a regression (make some feature that previously worked stop functioning as intended in a new release) are marked with "regression" tag at http://bugs.mysql.com.



      [Read more...]
    Fun with Bugs #26 - MySQL bugs Oracle had not fixed for me (yet)
    +0 Vote Up -0Vote Down
    In the previous post in this series I've listed 15 MySQL bug reports, documentation and feature requests I've made in 2013 that got fixes or any other kind of solution. Now it's time to check what happened to the rest and try to think why.

    First of all, no MySQL bug reporter is perfect (if only Domas), so some bug reports may be false alarms ("Not a bug"), to hard to fix at any foreseeable future ("To be fixed later") or

      [Read more...]
    Range access: now in an IN predicate near you.
    Employee_Team +2 Vote Up -0Vote Down
    Several users have reported that certain queries with IN predicates can’t use index scans even though all the columns in the query are indexed. What’s worse, if you reformulate your query without IN, the indexes are used. Let’s take some example query. Suppose we have a table with two indexed columns:CREATE TABLE t1 ( 
      col1 INTEGER,
      col2 INTEGER,
      …

      KEY key1( col1, col2 ) ); Let’s take a look at some queries that could take advantage of the key1 index to read rows without accessing the table.
  • SELECT col1, col2 FROM t1 WHERE col1 = 100;
  • SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200;
  • SELECT col1, col2 FROM t1 WHERE col1 > 100 AND col1 < 200 OR col1 > 300 AND col1 < 400;
  • SELECT col1, col2 FROM t1 WHERE col1 = 100 AND col2 > 100 AND cold2 <



  •   [Read more...]
    WITH RECURSIVE and MySQL
    Employee_Team +1 Vote Up -0Vote Down

    If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called “WITH clause” of SQL.
    Some call it Subquery Factoring. Others call it Common Table Expression. A form of the WITH CLAUSE, WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration. This can be quite useful to produce reports based on hierarchical data. And thus is an alternative to Oracle’s CONNECT BY. MySQL does not natively support WITH RECURSIVE, but it is easy to emulate it with a generic, reusable stored

      [Read more...]
    WITH RECURSIVE and MySQL
    Employee +2 Vote Up -0Vote Down
    If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called "WITH clause" of SQL.
    Some call it Subquery Factoring. Others call it Common Table Expression.
    In its simplest form, this feature is a kind of "boosted derived table".

    Assume that a table T1 has three columns:

    CREATE TABLE T1(
    YEAR INT, # 2000, 2001, 2002 ...
    MONTH INT, # January, February, ...
    SALES INT # how much we sold on that month of that year
    );
    Now I want to know the sales trend (increase/decrease), year after year:

    SELECT D1.YEAR, (CASE WHEN












      [Read more...]
    FAQ: InnoDB extended secondary keys
    Employee_Team +0 Vote Up -0Vote Down
    MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I’ll try to get rid of the confusion once and for all. Famous last words… here goes:


    Q1: Do I need to do anything to enable extended secondary keys?No, nothing at all. It’s on by default and I can’t see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch=’use_index_extensions={on|off}’.

     

    Q2: Does extended secondary keys only work with InnoDB?

    No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say
      [Read more...]

    FAQ: InnoDB extended secondary keys
    Employee +4 Vote Up -0Vote Down
    MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

    Q1: Do I need to do anything to enable extended secondary keys?

    No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

    Q2: Does extended secondary keys only work with InnoDB?

    No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say







      [Read more...]
    Fun with Bugs #23 - more on Optimizer bugs in MySQL 5.6
    +3 Vote Up -0Vote Down
    When I've sent CV to MySQL AB back in 2004 (or early 2005) I had actually wanted to become a developer there. As a person who just started to use MySQL on a regular basis and, at the same time, had to explain dozens of engineers per month how optimizers works in Oracle and Informix RDBMSes, and optimize queries for them from time to time, I was naturally interested in adding missing (but well known to me) features to MySQL optimizer (from hash joins to stored outlines, histograms and tracing, all things I've noted as extremely useful for many real life cases)... So, I wanted to work on MySQL optimizer specifically, if something related to MySQL at all.

    It happened so that MySQL Support had somehow noted my CV before anybody else, so in few months and after some serious tests and long screening talks (the longest of them was with new, at the time,

      [Read more...]
    Fun with Bugs #21 - recently verified bugs in MySQL 5.6.13
    +0 Vote Up -0Vote Down
    Notable contribution of MySQL Community to MySQL 5.6.13 was explicitly recognized recently. But users and contributors still continue their efforts, as well as Oracle engineers. Even though MySQL 5.6.13 has been generally available just for few days, we already have several new bug reports and updates to known bugs at http://bugs.mysql.com. Let me present a short list with some comments.

    • Bug #69915 is a great example of a "new thinking" inside Oracle. Todd Farmer does not only write about new ways to use PERFORMANCE_SCHEMA in MySQL 5.6 in his blog, but also reports


      [Read more...]
    Fun with Bugs #19 - waiting for MySQL 5.6.13 and some real fun?
    +0 Vote Up -0Vote Down
    I feel like MySQL 5.6.12 was released ages ago, while in reality it was on June 3, less than 2 months ago. No wonder I feel so, after writing several posts about bugs fixed and not fixed in it... Anyway, we still have to wait for MySQL 5.6.13 for a week or even two probably and in the meantime I decided to write new post for this series based on good old idea of making a digest of my recent bugs-related posts at Facebook. I know, it's boring and annoying (same as waiting for the release of 5.6.13).

    Let's start with Bug #69846 - "ICP does not work on UNIQUE indexes". Based on my quick tests presented there I'd say that ICP (

      [Read more...]
    Fun with Bugs #11 - Top 10 Optimizer Regression Bugs in MySQL 5.6
    +1 Vote Up -0Vote Down
    I've got a question from colleague last night on what bugs should users take into account if they plan to upgrade to MySQL 5.6 now. Simple answer is: it depends. If one of the new features or scalability improvements are really important, then bugs in other features or clearly identified problematic use cases may be just ignored or avoided.

    But to be on a safe side users should at least check if they are (or may be) affected by a known regression bugs, when new version is slower or produce wrong results or crashes in cases that worked without problems before.

    List of bugs in MySQL 5.6 that can be formally considered as regressions comparing to previous major versions would be long enough for a single post. So I'd like to concentrate on regression bugs in Optimizer here:





      [Read more...]
    MySQL Query Patterns, Optimized – Webinar questions followup
    +3 Vote Up -0Vote Down

    On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you missed it, you can still register to view the recording and my slides.

    Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as we had time for  during the session, but here are all the questions with my complete

      [Read more...]
    Optimizing MIN and MAX MySQL Functions
    +1 Vote Up -0Vote Down

    MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

    CREATE TABLE `history` (
       `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
       `u_id` int(10) unsigned NOT NULL,
       `cn_id` int(10) unsigned NOT NULL,
       `f_id` int(10) unsigned NOT NULL
       PRIMARY KEY (`h_id`)
     ) ENGINE=InnoDB

    If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

    mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G
     *************************** 1. row ***************************
                id: 1
       select_type: SIMPLE
             table: history
      [Read more...]
    Shard-Query 2.0 Beta 1 released
    +4 Vote Up -0Vote Down

    It is finally here.  After three years of development, the new version of Shard-Query is finally available for broad testing.

    This new version of Shard-Query is vastly improved over previous versions in many ways.  This is in large part due to the fact that the previous version of Shard-Query (version 1.1) entered into production at a large company.  Their feedback during implementation was invaluable in building the new Shard-Query features.   The great thing is that this means that many of the new 2.0 features have already been tested in at least one production environment.

    This post is intended to highlight the new features in Shard-Query 2.0.  I will be making posts about individual features as well as posting benchmark results.

      [Read more...]
    Webinar: SQL Query Patterns, Optimized
    +2 Vote Up -0Vote Down

    Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”

    Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query

      [Read more...]
    The saddest bug of them all (SQL is dead?)
    +2 Vote Up -0Vote Down

    From time to time I will observe servers wasting lots of CPU when doing batch row operations. In perf top it will look like this:

    8.24% mysqld [.] Arg_comparator::compare_int_unsigned()
    7.17% mysqld [.] Item_cond_and::val_int()
    4.37% mysqld [.] Item_field::val_int()
    4.37% mysqld [.] Item_cond_or::val_int()
    2.90% mysqld [.] MYSQLparse(void*)
    2.64% mysqld [.] Item::val_bool()

    Essentially if you construct queries like (a=1 AND b=2) OR (a=3 AND b=4) ..., at large enough batch size evaluating the WHERE will become far more expensive than anything else (yes, more expensive than decompressing rows or doing all the InnoDB magic and what not).

    MySQL has awesome syntax that makes certain batch lookups much faster: WHERE a IN (1,2,3). It constructs a tree that then each row can be compared against and one does not have to iterate through

      [Read more...]
    Fixing awkward TIMESTAMP behaviors...
    Employee +3 Vote Up -0Vote Down
    There are great features in MySQL 5.6. But not only that. We also tried to correct some old behaviors and limitations which, over the years, have shown to irritate our Community. The behavior of TIMESTAMP columns is one of them.

    My colleague Martin Hansson did most of the work and summarized it well in his blog. Thanks to him, since MySQL 5.6.5, it's possible to declare more than one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Two limitations lifted!

    But that is not the end of the story. TIMESTAMP was still special. Unlike other datatypes, if not declared with the NULL or NOT NULL attributes, it would automatically get



      [Read more...]
    About MySQL 5.6
    +14 Vote Up -2Vote Down

    I am very excited and thrilled to use the latest release of MySQL 5.6 in production. This is probably the most notable and innovative release in many years, if not ever.

    During the last year, we had the chance to work with many new features and test the fixes to old issues. To be honest, I was expecting to have MySQL 5.6 GA before now, and I even wagered with my colleague Francisco that it would be out before the end of 2012. It was nothing special, just a beer in the Santa Clara Hyatt lounge. Unfortunately for me, MySQL 5.6 is now in GA and given that it happened in 2013, I lost the bet and now have to pay for that beer. But I have also lost the full list of things that we saw as relevant, interesting, or really innovative for MySQL.

    So I took a step back, took some time, and reviewed what Oracle delivered in this new MySQL release.

    Short premise

    Oracle developer teams

      [Read more...]
    When kill flag is checked for SELECT? Part II
    +2 Vote Up -0Vote Down
    In the previous part I've stopped at the moment when we entered JOIN:exec() - most checks for kill flag happen somewhere there, during query execution. We know the list of functions that checks this flag during query execution:

    sub_select_cache()
    evaluate_join_record()
    flush_cached_records()
    end_write()
    end_update()
    end_unique_update()
    end_write_group()
    remove_dup_with_compare()
    remove_dup_with_hash_index()


    but we do not know when exactly each of them is called. So, let me try to show what happens inside JOIN::exec (some code paths and checks are not considered for simplicity, we care about SELECT, but not EXPLAIN SELECT etc). I've included











      [Read more...]
    Showing entries 1 to 30 of 70 Next 30 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.