Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems.
Here is what you need to know about ORDER BY ... LIMIT optimization to avoid these problems

ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many web sites you will fine top tags, recently registered users etc - which would often require ORDER BY with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ..... WHERE [conditions] ORDER BY [sort] LIMIT N,M

Make sure it uses index It is very important to have ORDER BY with LIMIT executed without scanning and sorting full result set, so it is important for it to use index - in this case index range scan will be started and query execution stopped as soon as soon as required amount of rows generated.

…

A lot of time, I hear discussions about which reporting tool is the easiest to use for certain special tasks. Most of the time, I just ignore these “threads” because it’s not my cup of tea as a developer of ETL solutions.
However, it has to be said, often the solution to complex reporting requirements is to be found in ETL.
When you find yourself struggling with complex reports that need any of the following:

• compare different records
• aggregate beyond simple sums and averages.
• report on a non-existing records (report 0 sales, etc)
• …

Well, in those cases you need ETL.

Let’s take for example the case of the reporting on non-existing sales: how can you report that there has been 0 sales for a certain product during a certain week? Well, you can create an aggregate table in your ETL that contains the following:

• Dimensions
  …

While there are some major annoyances with transactions in MySQL like causing an implicit commit when doing DDL statements, there are also slightly more subtle annoyances. A user just stumbled over a little annoyance in MySQL transaction handling and autoincrement. If you generate a new ID inside a transaction and rollback, the autoincrement counter is not decremented.

Of course I know that autoincrement is just about generating new unique id's and I should not worry about gaps and that for most people the value range for INT is more than they will ever need, not to mention BIGINT (though BIGINT really sucks in PHP as its larger than the natively supported value range for integers in PHP). However this issue goes to show again that a ROLLBACK is not the magic undo-wand that people often expect.

mysql> create table autoinc (sequence int auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.59 sec)

mysql> …

Two days ago we added the University of Illinois/NCSA Open Source License to the list of licenses in the MySQL FLOSS license exception. In my opinion exception is actually a badly chosen word - extension would probably be more sufficient here as it actually extends the scope of the MySQL client libraries license. From the introductionary text on top of the document:

We want specified Free/Libre and Open Source Software ("FLOSS") applications to be able to use specified GPL-licensed MySQL client libraries (the "Program") despite the fact that not all FLOSS licenses are compatible with version 2 of the GNU General Public License (the "GPL").

In other words, the license exception allows the distribution of a derived work including the MySQL Client …

Two days ago we added the University of Illinois/NCSA Open Source License to the list of licenses in the MySQL FLOSS license exception. In my opinion exception is actually a badly chosen word - extension would probably be more sufficient here as it actually extends the scope of the MySQL client libraries license. From the introductionary text on top of the document:

We want specified Free/Libre and Open Source Software ("FLOSS") applications to be able to use specified GPL-licensed MySQL client libraries (the "Program") despite the fact that not all FLOSS licenses are compatible with version 2 of the GNU General Public License (the "GPL").

In other words, the license exception allows the distribution of a derived work including the MySQL Client …

database, declarative, procedural, SQL transactions

Developers* seem to think of a transaction as a block of code. A try/catch block seems analogous at first glance — try to do a series of actions, and if one fails, roll them all back, else commit. So many people trip up and try to write code within a transaction to avoid deadlock or race conditions.

This is the source of the problems with the commands that perform an implicit COMMIT (that links to the 4.1 and under …

database, silent failure transactions

At the July MySQL User Group Meeting, Jim Starkey wondered aloud, “What happens when I COMMIT on a memory table?” I wrote the question down, to research it later.

The obvious answer is “COMMIT on a non-transactional table does nothing.”

Tonight I was thinking about this, and I realized I do not actually COMMIT “on a table.”

The manual page at: …

Do you know how to best allocate the data nodes to different computers to get maximum availability? Or do you know how to do it, but are curious about why it should be done in that particular way? I just read an internal discussion at MySQL and realized that people might be interested in this.

Assumptions
Lets assume that we have a MySQL Cluster with twelve data nodes (ndb processes) and two replicas (copies) of each data fragment. This means that there are twelve ndbd processes that store the data and that each piece of information (fragment) exists in two copies.

Then assume that six fragments of data is distributed on the data nodes as follows:

• Data nodes D1 and D2 store the same information (fragment F1),
• D3 and D4 store F2,
• D5 and D6 store F3,
• D7 and D8 store …

The latest issue of (IN)SECURE magazine, a freely available digital security magazine discussing some of the hottest information security topics, is featuring a reprint of my introduction to MySQL Sandbox.

I came across this opinion piece by Paul Barton, an attorney at Field Fisher Waterhouse LLP today. I wish he would have attended the Open Source Business Conference before writing his piece. (OSBC includes, among other things, two full days of legal education on open source.) He could have saved himself the embarrassment of misinformation. (I won't call it malpractice. :-)

(Btw, I am an attorney. I don't play one on TV.) (Unfortunately.)

First off, Paul is clearly talking about "in the wild" open source, whereas most enterprise open source adoption is of commercial open source (Red Hat, MySQL, JBoss, etc. etc.). It's true that Red Hat doesn't own the code (or most of it, anyway) that it ships, but this is emphatically not true of virtually every other piece of commercial open source …