TL;DR: Priority queue optimization for filesort with small LIMIT is now visible in MariaDB: there is a status variable and you can also see it in the slow query log (KB page link).

A longer variant:
One of the new optimizations in MySQL 5.6 is ability to use a priority queue instead of sorting for ORDER BY … LIMIT queries. The optimization was ported into MariaDB 10.0 long ago, but we still get questions if/when it will be ported. I guess, the reason for this is that, besides the query speed, you can’t see this optimization. Neither EXPLAIN, nor EXPLAIN FORMAT=JSON or PERFORMANCE_SCHEMA or status variables give any indication whether filesort used priority queue or the regular quicksort+merge algorithm.

MySQL 5.6 has only one way one can …

This post appeared first on mysqlserverteam.com

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. ExampleUsing 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);

…

I had the pleasure of presenting to the PHP Users Group Philippines a few days ago about mysqlnd_ms. The mysqlnd plugin, MySQL Master Slave, is a transparent layer on top of mysqlnd extension. This allows you to do read-write splitting and slave reads load balancing without needing to change anything from your application. But do you know you can also achieve a form of high availability with this plugin? I shared 2 forms on my presentation, using async MySQL replication either in master-slave configuration or master-master configuration, while the second form is having an all primary cluster where you can write to all nodes.

This first part is to demonstrate how you can achieve a simple HA solution using the first form. First, all the sample code here can be found on my GitHub repository. So, to use the mysqlnd_ms plugin, it uses …

InfiniDB High Performance Analytic Query Engine for MySQL and SQL-on-Hadoop Powers Faster Data Access and Insights with Tableau Software

On Thursday 17th July I’ll be hosting a webinar which explains what MySQL Clusrter is, what it can deliver and what the latest developments are. As always the webinar is free but please register here.

Details:

Join this technical webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can help your products and applications meet the needs of the most demanding markets. MySQL Cluster combines these …

The binary and source versions of MySQL Cluster 7.3.6 have now been made available at http://www.mysql.com/downloads/cluster/.

Release notes

MySQL Cluster NDB 7.3.6 is a new release of MySQL Cluster, based
on MySQL Server 5.6 and including features from version 7.3 of the
NDB storage engine, as well as fixing a number of recently
discovered bugs in previous MySQL Cluster releases.

Obtaining MySQL Cluster NDB 7.3. MySQL Cluster NDB 7.3 source
code and binaries can be obtained from
http://dev.mysql.com/downloads/cluster/.

For an overview of changes made in MySQL Cluster NDB 7.3, see
MySQL Cluster Development in MySQL Cluster …

InfiniDB High Performance Analytic Query Engine for MySQL and SQL-on-Hadoop Powers Faster Data Access and Insights with Tableau Software

Get the best from both worlds with bundled packages: a training course and a certification exam voucher. You save 20% when you buy training and certification in discount packages.

Training helps you acquire product expertise. Certification proves this expertise to your future employers.

There are two MySQL Discount Packages: MySQL Database Administration and MySQL Developer.

For more information about the authentic MySQL curriculum, go to …

Mon, 2014-07-14 08:30anderskarlssonIntroduction

This is an introduction to MariaDB Replication and to why we need a binlogs server and what this is. The first part is an introduction to replication basics, and if you know this already, then you want want to skip past the first section or two.

MariaDB Replication

MySQL and MariaDB has a simple but very effective replication system built into it. The replication system is asynchronous and is based on a pull, instead of a push, system. What this means in short is that the Master keeps track of the DML operations and other things that might change the state of the master database and this is stored in what is called the binlog. The slave on the other hand is responsible for getting the relevant information from the master to keep up to speed. The binlogs consist of a number of files that the master generates, and the traditional way of dealing with slaves is to point them to …

Presentations/VideosConferencesGeneral

At the 2008 MySQL Conference and Expo, the closing keynote was Jacek Becla speaking on The Science and Fiction of Petascale Analysis. See the slides on the O'Reilly Conference Site, and the Official Conference Description