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

Displaying posts with tag: optimizer (reset)

Q&A: Even More Deadly Mistakes of MySQL Development
+1 Vote Up -0Vote Down

On Wednesday I gave a presentation on “How to Avoid Even More Common (but Deadly) MySQL Development Mistakes” 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

  [Read more...]
A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements
Employee_Team +2 Vote Up -0Vote Down

In MySQL, query optimization of single-table UPDATE/DELETE statements is more limited than for SELECT statements. I guess the main reason for this is to limit the optimizer overhead for very simple statements. However, this also means that optimization opportunities are sometimes missed for more complex UPDATE/DELETE statements.

Example

Using the DBT-3 database, the following SQL statement will increase prices by 10% on parts from suppliers in the specified country:

UPDATE part
SET p_retailprice = p_retailprice*1.10
WHERE p_partkey IN
     (SELECT ps_partkey
      FROM partsupp JOIN supplier
      ON ps_suppkey = s_suppkey
      WHERE s_nationkey = 4);

Visual EXPLAIN in MySQL Workbench (http://www.mysql.com/products/workbench/) shows that the optimizer will choose the

  [Read more...]
Re-factoring some internals of prepared statements in 5.7
Employee +1 Vote Up -0Vote Down
[ this is a re-posting of what I published on the MySQL server team blog a few days ago ]
 
When the MySQL server receives a SELECT query, the query goes through several consecutive phases:
  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains



  [Read more...]
Re-factoring some internals of prepared statements in 5.7
Employee_Team +5 Vote Up -0Vote Down

When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or outer joins are involved).
  • optimization: finding the best way to read tables: the best order of tables, and for each
  [Read more...]
Dynamic range access (and recent changes)
Employee_Team +2 Vote Up -0Vote Down

Dynamic range access (aka “Range checked for each record” since that is what EXPLAIN will say in the ‘Extra’ column) is one of the big mysteries to MySQL users. The reason is that it is used for queries that are less common, such as queries with non-equality join predicates. The following query is an example; it finds the number of messages sent since the user was last logged in:

EXPLAIN SELECT user.user_id, COUNT(message.id)
FROM message, user
WHERE message.send_date >= user.last_activity
GROUP BY user.user_id;

However, although such join predicates are less common, it doesn’t mean that the dynamic range access method is not important.

How the dynamic range access method works

It is easier to explain dynamic range access if we consider a “normal” join first, so let’s take a look at this

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