Somedays you just cannot win :)

First some of the things that you need to use and understand

Explain Syntax

Order by Optimization

Group by Optimization

Update: Updated errors.

Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, and tight index scan.

Loose index scan, scans the entire table index, while tight index scan uses some sort of constraint. For large datasets that are accessed often and require some sort of group by, tight index scans are better.


So how to pick columns to create …

Fun, and all I wanted was some news - because I have no idea what everyone else's blog urls are.

As Paul points out, this new erlrc project is very exciting news. One of the most interesting features of Erlang is how you can do hot code updating, and getting integrated into the package manager is absolutely wonderful. Anyone working on getting this into Ubuntu yet? There is a very nice howto written about how to set up your Erlang app with this. I’m looking forward to setting this up on my mini-cluster of slicehost nodes.

Simple auditing, i.e., knowing what changed recently, can save you tons of time while troubleshooting.

I know that, in the ideal world:

• Everything is supposed to be done through configuration management.
• Everything is documented and all changes are tracked through a VCS.
• Every DDL or set global is trapped via MySQL Proxy and logged.

But there are always ways to bypass the gatekeepers. Changes can go in unnoticed. An hour or so later, your database performance suddenly changes for the worse, and you get that phone call.

First you check if anything caused an actual error. You look around at a few log files and nothing shows up. The next thing you ask yourself is, did someone change anything in the last little while. Of course, everybody says no. After a few hours of digging, comparing schemas, diff-ing old and current config files, you actually find what has changed, …

On Thursday, June 12, at 15:00 CEST (14:00 GMT), there will be a MySQL University Lesson on MySQL Sandbox, a tool to install one or more side server in a few seconds.

To attend the lesson, follow the instructions for attendees and download the recommended material.

I haven't been a big user of the MySQL Forums till recently.I'm not sure why - partly because I like to think I know something about what I do ( I just need to remember that there is always someone else who knows more), partly because I have a need to re-invent the wheel as often as possible, and partly because I dont' like waiting around for an answer...It can be a little overwhelming when you

I would like to say a big Thank-You to the community. It has been quite a ride this last year for my team and me, designing and coding MySQL Workbench, getting it to GA quality, presenting at the UC and constantly continue to improve quality since then. Now let’s look at the facts.

• I am happy to announce that we have reached more than 400.000 downloads of MySQL Workbench today, which is quite a lot for a highly specific tool in such a short time-frame (406690 at 18:25 GMT+1 to be exact).
• We have about as many downloads a day as the MySQL Server 5.1 server on Windows, also quite impressive I think for a little database design tool (I am only comparing Windows since Workbench is only available on Windows yet).
• The number of incoming bugs has been going down after each new release and we are now at a constantly low level. Nevertheless we are continuing our bug-fix releases while working on MySQL Workbench 5.1, …

We run an internally developed AMP benchmark to understand the performance characteristics of the AMP stack on various platforms.

We did some comparisons of the AMP stack in Cool Stack 1.2 with the one in Cool Stack 1.3. We didn't see any signficant difference in performance in the Apache/PHP tier between the two versions. However, there is a dramatic improvement in performance in the MySQL tier using Cool Stack 1.3. Cool Stack 1.2 shipped with MySQL 5.0.45 and 1.3 now has MySQL 5.1.24.

The graph below shows the CPU utilization on the MySQL system at different load levels :

MySQL 5.1 is 4x more efficient than 5.0.45 ! There are improvements in the Query Cache and query optimization in MySQL 5.1, but at this point we are doing more analysis to understand the reasons for the dramatic performance increase. Here is another graph that shows the network …