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 …

You have already heard the great news that you can now prove your knowledge of MySQL Server 5.6 with the new MySQL certification exams:

• Oracle Certified Professional, MySQL 5.6 Developer
• Oracle Certified Professional, MySQL 5.6 Database Administrator

Until December 21th 2013, these exams are beta phase so you get a fully-fledged certification at a much reduced price; for example $50 in the United States or 39 euros in the euro zone.

There is a lot of excitement around these new …

Character sets are like the force: they surround us and penetrate us, binding all our digital world together. A character set is how we convert the 1’s and 0’s that the computer understands into human-readable characters like ABC. In one of the first character sets, ASCII, the number 97 is translated to “a” and 63 is the question mark (?).

“Are there other languages besides English?”
“Don’t think so, Bob.”

English-Only Please

The trouble with ASCII is that it was created back in the ‘60s by a bunch of Americans and they were not thinking about French or German, they were thinking about English. Guess what? ASCII works great for American English, but there’s no way it can encode even Spanish, let alone something like Chinese, so it only met the needs of around 5% of the world …

We’ve been working hard lately at CakeMail on building the new version of the application. While building this new version, we have been rebuilding the API from scratch, reviewing every queries. A set of these queries were to derive the status of a subscriber based on the logs of a campaign.

Table Structure

Here is the table structure we will be using for this post.

Logs

I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done.

One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL".

I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago.

I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code.

So I need a short refresh.

To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using.

• CIL 1.0: mcs
• CIL 2.0: gmcs
• CIL 4.0: dmcs

For Ubuntu …

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys. This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various important places throughout the InnoDB code and as such any contention on the dict_sys mutex is going to have a InnoDB system-wide negative affect. You can read the rest of the post here.

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys. This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various important places throughout the InnoDB code and as such any contention on the dict_sys mutex is going to have a InnoDB system-wide negative affect.

The post InnoDB scalability issues due to tables without primary keys appeared first on ovais.tariq.

I previously wrote that I am studying for the MySQL 5.6 exam, and that I’m less confident in my skills as a database developer. When I went through the list of topics in the exam, one thing I knew I would have to study is stored programs.

So first, some definitions. From the manual:

Stored programs include these objects:

• Stored routines, that is, stored procedures and functions. A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. A stored function is used much like a built-in function. you invoke it in an expression and it …