Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 99 10 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 “ …

  [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 …

  [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 …

  [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 …

  [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 …

  [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 | ... …





  [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 …

  [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 …

  [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 …

  [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 …

  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 99 10 Older Entries

Planet MySQL © 1995, 2016, 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.