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.

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 (http://mysql.com/news-and-events/web-seminars/display-583.html).

NDB Pushed JOINs means query shipping instead of data shipping and it reduces drastically the network hops between the MySQL Server and data nodes, which in turn gives a tremendous performance improvement. For particular


  [Read more...]

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 talk about Batched Key Access at MySQL User Conference, and the slides are available here.
• There is now Batched Key Access

  [Read more...]

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.

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 ((`test`.`t2`.
`a` = `test`.`t1`.`a`)) where 1

In this query LEFT JOIN is not converted to inner.

Now let's try a query where outer join will be converted:

mysql> explain extended select * from

  [Read more...]