We already discussed one to one relations in MongoDB, and the main conclusion was that you should design your collections according to the most frequent access pattern. With one to many relations, this is still valid, but other factors may come into play.

Let’s look at a simple problem: we are a shop and we want to store customers’ information as well as their orders. Each customer can make several orders, this is a one to many relation. With MySQL or any relational database system, we would create 2 tables:

CREATE TABLE customer (
  customer_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL DEFAULT '',
  zipcode varchar(10) DEFAULT NULL,
  PRIMARY KEY (customer_id)
) ENGINE=InnoDB;
CREATE TABLE orders (
  order_id int(11) NOT NULL AUTO_INCREMENT,
  customer_id int(11) NOT NULL DEFAULT '0', …

Percona is glad to announce the release of Percona Playback 0.7 for MySQL on October 22nd, 2013. Downloads are available from our download site and Percona Software Repositories.

Percona Playback for MySQL is a tool for replaying the load of one database server to another. Currently it can read queries from MySQL query-log and MySQL tcpdump files and run them on other MySQL server. With Percona Playback you can measure how a server or database upgrade, change in my.cnf or schema change can affect the overall performance of your MySQL database server.

Percona Playback for MySQL can also help evaluate new server versions, patches and hardware against …

As you might have noticed, we have released a framework for managing farms (or grids, as Justin suggested) of MySQL servers called MySQL Fabric. MySQL Fabric is focused on being easy to use and extensible, and two extensions are currently part of the framework: one to manage high-availability and one to implement sharding.

High-Availability Groups One of the central concepts used to construct a farm is the high-availability group (or just group when there is no risk of confusion) and is introduced by the high-availability extension. As mentioned in the previous post, the group concept does not really represent anything new but is rather a formalization of how we think and work with the structure of the …

Come learn how to tune MySQL 5.6 servers, the basics of query optimization, and learn how to use the new Visual Explain with Workbench 6.0.

Date: Monday, November 04, 2013
Time: 6:00 PM – 8:00 PM

Oracle Corporation
6031 Connection Drive, Suite 900
Irving, TX 75039-

Event is free to the public but please RSVP
Pizza at 5:30 p.m. Presentation at 6:00 p.m.

Presented by: Dave Stokes, MySQL Community Manager North America, Oracle Corporation

On Wednesday I’ll be leading a webinar exploring MySQL 5.6’s new replication features. And yes, as usual I’ll deliver news on the good, the bad and the ugly (that is to say the benefits, limitations and challenges).

The webinar, appropriately titled, “New Replication Features in MySQL 5.6: Benefits, Limitations, and Challenges“, is scheduled for Oct. 23 at 10 a.m. Pacific Daylight Time. You can register now to reserve your spot (this webinar will also be available for playback afterward).

This session aims at exploring some of these …

For those who were unaware, in MySQL the following statements are currently identical:

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;
SELECT MAX(Population), CountryName FROM City GROUP BY CountryName ORDER BY CountryName;

That is to say that regardless of whether or not you asked for it, whenever you chose to GROUP BY, you will also have data sorted and returned in that order too.

The problem with this, is that it can result in worse performing queries. Sorting either reduces the number of execution plans possible, or requires an additional step to sort the data. Which is why many DBAs advocate writing group by queries with ORDER BY NULL. i.e.

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;

Should …

Geo-enabled (or location enabled) applications are very common nowadays and many of them use MySQL. The common tasks for such applications are:

• Find all points of interests (i.e. coffee shops) around (i.e. a 10 mile radius) the given location (latitude and longitude). For example we want to show this to a user of the mobile application when we know his/her approximate location. (This usually means we need to calculate a distance between 2 points on Earth).
• Find a ZIP code (U.S. Postal address) for the given location or determine if this location is within the given area. Another example is to find a school district for the given property.

MySQL had the spatial functions originally (implementation follows a subset of OpenGIS standard). However, there are 2 major limitation of MySQL spatial functions …

MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I'll try to get rid of the confusion once and for all. Famous last words... here goes:

Q1: Do I need to do anything to enable extended secondary keys?

No, nothing at all. It's on by default and I can't see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch='use_index_extensions={on|off}'.

Q2: Does extended secondary keys only work with InnoDB?

No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say "should" because it requires a minimum of work from the storage engine provider; it …