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

Displaying posts with tag: how-it-works (reset)

Optimizations for derived tables in MySQL 5.6 and MariaDB 5.5+
+5 Vote Up -1Vote Down

I had been involved with subquery optimizations fairly closely, but last week I was surprised to find out that MySQL 5.6 does not support derived table merging. This feature was among the subquery features in the abandoned MySQL 6.0. In MariaDB, it was finished and released as part of MariaDB 5.3/5.5. As for MySQL, neither MySQL 5.6, nor MySQL 5.7 has this feature.

So what is this “derived merge”? It’s simple to understand. When one writes complex queries, it is common to use …

  [Read more...]
Slides: New subquery optimizations in MySQL 6.0 (new revision)
+0 Vote Up -0Vote Down

I'm now at MySQL Conference and have just finished my New Subquery Optimizations in MySQL 6.0 talk. If you're eager to get the slides, here is the link to the pdf file and a mirror.

The talk has the same name as this MySQL University session but the slides have been thoroughly re-worked, and also …

  [Read more...]
Slides: New subquery optimizations in MySQL 6.0
+0 Vote Up -0Vote Down

A bunch of (hopefully) self-explanatory slides about new subquery optimizations in MySQL 6.0 is available here (UPDATE: here's a working link). The slides are from this MySQL University session, so there was an audio stream but there were some difficulties with it and it is not available now.


  [Read more...]
Slides and audio: How MySQL Handles ORDER BY, GROUP BY, and DISTINCT
+0 Vote Up -0Vote Down

I haven't posted anything for a while, but that doesn't mean that nothing was happening around MySQL Optimizer. In fact, exactly the reverse was true - so many thing were happening that I didn't have time to blog.

I'll try to make up for the lost coverage. Let's start from smaller things. On Nov, 1 I've had a MySQL University session titled How MySQL Handles ORDER BY, GROUP BY, and DISTINCT.

The session page has slides and audio. If you're ready to spend 1 hour on MySQL's ways of doing …

  [Read more...]
How MySQL executes ORDER BY
+0 Vote Up -0Vote Down

In last couple of weeks there has been a tide of ORDER/GROUP BY-related optimization bugs, where I was the fixer or the reviewer. This wasn't an easy job because there is no sane description of how GROUP BY/ORDER BY handling is supposed to work.

To figure it out, I had to write an explanation of how it works. The first part is about ORDER BY. Hopefully there will be subsequent parts that will show how GROUP BY is related to ORDER BY and how it works.

Available means to produce ordered sequences

MySQL has two methods to produce ordered streams.

The first is to use a "range", "ref" or "index" access method over an ordered index. For …

  [Read more...]
Use of join buffer is now visible in EXPLAIN
+0 Vote Up -0Vote Down

UPDATE:
* s/Using join cache/Using join buffer/, changed to show the final variants of EXPLAIN output as described here
* s/join_buff_size/join_buffer_size/


Starting from 5.1.18, EXPLAIN output may show "Using join cache", like in this example:

mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar';
+----+-------------+-------+-------+-...-+--------------------------------+
| id | select_type | table | type  |     | Extra                          |
+----+-------------+-------+-------+-...-+--------------------------------+
|  1 | SIMPLE      | t1    | range | …




  [Read more...]
MySQL Optimizer and Prepared Statements
+0 Vote Up -0Vote Down

Performance-wise, the idea of Prepared Statements is that the server does certain pre-processing on PREPARE command, and then those steps are omitted when the statement is executed. If the statement is executed several times, we get an economy of

cost_of_prepare_preprocessing * (#statement_executions - 1)

This makes one want to move the CPU and IO-intensive query optimization into the PREPARE phase. Unfortunately, this will make the optimizer work much worse - optimizer's decisions are based on the external information, and there is much less available information at PREPARE phase. The most crucial differences are that

  • The values of the '?' parameter markers are …
  [Read more...]
How to find out if an outer join was converted to inner
+0 Vote Up -0Vote Down

After this post I've got a question how one can tell if his outer join was converted to inner. You can find it out by looking at the warning generated by EXPLAIN EXTENDED. If the outer join wasn't converted, you'll see it in the rewritten query in the warning:

mysql> explain extended select * from t1 left join (t2, t3) on t2.a= t1.a;
...
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a`
AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on …
  [Read more...]
Showing entries 1 to 8

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.