My latest quiz was quite popular, and some interesting ideas were submitted.
There was an interesting development. A colleague called and asked me for advice on how to insert 4 billion rows in a table with a simple structure.

create table t1 (
id tinyint not null
);

Very simple. No primary key, no indexes. It is needed to perform some specific tests.
Actually, not 4 billion, but 2^32 records are needed, i.e. 4,294,967,296.
The classical method used in these cases is doubling the table contents:

insert into t1 values (1),(1),(1),(1),(1),(1),(1),(1);
insert into t1 select * from t1;
insert into t1 select * from t1; # and so on

My solution was similar to the one from my quiz.

CREATE VIEW `v4` …

Imagine you want to do something like this in a stored procedure:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
END CASE;

And it works fine for a while until you get and error:

ERROR 1339 (20000): Case not found for CASE statement

This happens due to the fact there is no WHEN clause to handle this specific value. It's unlike the regular switch clause logic in C, but it seems reasonable. So I've tried adding an empty ELSE clause that would do nothing:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
ELSE --or ELSE; or ELSE NULL; or any combination you might think of
END CASE;

Nope, we get the usual "You have an error in your SQL syntax; check the …

MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular and some implementation can be different but I hope this will let you to understand MVCC a bit better.

Transaction ID As the name says this is transaction identifier. It can be used by the engine for many things - for lock handling to see which transaction holds the lock and possibly kill it in case of deadlock, for proper isolation mode handling - transaction should see its own uncommitted changes but other transactions typically do not see them as well as MVCC implementation. It also can be used for recovery so you can see to which transaction given change corresponds so you can roll back or redo changes for …

Looking through the output of the latest benchmark run of libmemcached I just noticed this:

Nonblock
Testing mdelete_generate 1.119 [ ok ]
Testing delete_generate 0.983 [ ok ]

This means that the non-blocking version of the library is a tad faster then the multi delete support.

So I ask myself, is the multi delete support of value then? Does it matter if the above is true to have multi methods?

Semantic sugar is fine, but I am really wondering if it makes an ounce of sense.

I have the multi-set functions done, but I am wondering now if I should bother with committing them.

They add 3.5 K to the size of the library, which is not much.

More important is the question of whether or not they extend the library just a little too much. Each function just increases complexity. If someone can get the same performance by just writing a …

My latest post on generating one million records received many comments, with interesting solutions.
The challenge required the insertion of 1 million records in a simple table, with a few constraints.

create table t1 (
dt datetime not null,
primary key (dt)
);

The solutionThe official solution is straightforward:

create view v3 as select null union all select null union all select null;
create view v10 as select null from v3 a, v3 b union all select null;
create view v1000 as select null from v10 a, v10 b, v10 c;
set @n = 0;
insert into t1 select now()-interval @n:=@n+1 second from v1000 a,v1000 b;

You can appreciate the title …

I will present a session on Testing PHP/MySQL Applications with PHPUnit/DbUnit at the MySQL Conference & Expo 2008 that is held April 14-17 2008 in Santa Clara, CA, US.Testing PHP/MySQL Applications with PHPUnit/DbUnit
In the last decade, PHP has developed from a niche language for adding dynamic functionality to small websites to a powerful tool making strong inroads into large-scale Web systems. Critical business logic like this needs to work correctly. But how do you ensure that it does? You test it, of course.

To make code testing viable, good tool support is needed. This is where PHPUnit comes into play. …

Giuseppe Maxia’s new article at the MySQL DevZone rightly points out that using partitions doesn’t work by magic; as with any other enhancement, it requires a bit of know-how to realise its value. MySQL 5.1 Partitions in Practice reviews some of the basics and points out a few gotchas (if you want to partition on a DATE or DATETIME column, pay close attention here). He then provides some realistic examples showing how you actually get partition pruning working for your queries and backs it up with some interesting performance figures.

On a related note, I recently reorganised the MySQL Manual’s discussion of functions in …

I've started some attempts at coding ha_trace storage engine I've mentioned earlier. One of the first things that became apparent was that I needed a way to put a hook into table name resolution code so I can wrap tables into ha_trace objects.

The need to intercept table name resolution and do something other than looking at the .frm files is not unique to call trace engine:

• Remote storage engines would benefit also:
  • NDB has a whole chunk of code that ships .frm files from one connected mysqld instance to another. It doesn't hook into name resolution; it ships table definitions proactively, which could be nuisance if you use a new mysqld node to just connect and run a few queries
  …

A

What are the skills of a MySQL DBA? Here are my 2 cents.

1. Solid understanding of database system including design and how data is processed.
2. Handy scripting language that you can use like PERL, Python, or even Shell.
3. MySQL Backup, High Availability and Performance Tuning.
4. SQL language.