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

Displaying posts with tag: joins (reset)

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...]
MySQL: Using Views as Performance Improvement Tools
+0 Vote Up -0Vote Down

The most basic and most oft-repeated task that a DBA has to accomplish is to look at slow logs and filter out queries that are suboptimal, that consume lots of unnecessary resources and that hence slow down the database server. This post looks at why and how VIEWs can help against such suboptimal operations.

Pushed down JOINs - Webinar
+3 Vote Up -0Vote Down

On Thursday, November 04, at 0900PST/1700CET/1600GMT there is a webinar about Pushed Down Joins. This webinar will explain how Pushed Down Joins works, and some performance numbers.
Register here: http://mysql.com/news-and-events/web-seminars/display-583.html.

NDB Pushed JOINs means query shipping …


  [Read more...]
Trying out MySQL Push-Down-Join (SPJ) preview
Employee +4 Vote Up -1Vote Down

At the 2010 MySQL User Conference, Jonas Oreland presented on the work he’s been doing on improving the performance of joins when using MySQL Cluster – the slides are available for download. While not ready for production systems, a preview version is available for you to try out. The purpose of this blog is to step through  testing an example query as well as presenting the results …

  [Read more...]
Code and slides: Batched Key Access feature preview
+0 Vote Up -0Vote Down

At the moment, there are two big features in the works in MySQL optimizer - Subquery optimizations and Batched Key Access. While the former is a part of MySQL 6.0, I wrote about it here in my blog, and so forth, the latter was in nearly stealth mode until a couple of weeks ago.

That's no longer the case:

  • Batched Key Access source code is now published as mysql-6.0-bka-preview tree.
  • Igor Babaev, the author of the feature, gave a …
  [Read more...]
Nested-loops join speedup idea promoted to WL task
+0 Vote Up -0Vote Down

The idea how to speed up nested-loops join a little I've mentioned earlier has now been promoted into a worklog entry. It is now known as WL#3724 "Short-Cutting Join Execution: Speeding up star queries" and its text is publicly available at MySQLForge.

At the moment there is only a short description, but hopefully Martin Hansson (the assigned developer) will add more content there.

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 7

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.