In our project (the secret merlin project) at MySQL I had to fetch entries from the table which specified by a time-span with a start and a end time:

root@localhost [merlin]> select * from dc_INTEGER; 
+---------+------------+-------+---------------------+---------------------+
| data_id | collect_id | value | begin_time          | end_time            | 
+---------+------------+-------+---------------------+---------------------+
|       1 |          3 |     0 | 2005-10-12 13:28:04 | 2005-10-13 13:43:25 |
|       2 |          1 |    94 | 2005-10-12 15:46:01 | 2005-10-13 10:22:28 |
|       3 |          1 |    95 | 2005-10-13 10:22:28 | 2005-10-13 11:15:59 |
|       4 |          1 |    94 | 2005-10-13 11:15:59 | 2005-10-13 13:43:31 |
+---------+------------+-------+---------------------+---------------------+

The ranges never overlap for the same collect_id.

Now the problem was: How does the query look like if I want to fetch time-span …

OK, so I am going to get this out of the way right now.

Yes, we changed the name of the conference. From "Users Conference" to "Conference and Expo". Why? To draw a larger crowd. Don't read into the removal of the "Users" bit. The focus of the conference is, and will remain, giving the user community what they want: quality technical sessions, tutorials, keynotes, interaction with MySQL developers and others users, and the friendly, personal atmosphere that the MySQL conference is known for.

So, Submit Your Proposals

Yes, it's that time of year again. But, hey, some things are a little different this year. I will be chairing the program committee for next year's conference (Arjen did a fabulous job last year). Secondly, the conference website has been updated. The following are the things I want folks to focus on when …

We have released solidDB for MySQL Beta 3. It is primarily a bug fixing release. We’ve also updated our documentation. If you like having an alternative to the existing standard MySQL …

I got bitten by an implicit type conversion with MySQL recently. This article is about avoiding implicit type conversions, and the weird places you might find them.

Is there anyone using partitioned archive tables in MySQL 5.1 for storing logs (or other voluminous data)?

Storing large amounts of logs in a relational database can bring special challenges.

Logs can take up huge amounts of space on disk, and while disk space is cheap, disk I/O can be expensive, performance-wise. But many logs compress really well, and for this the MySQL archive storage engine (insert-only, no indexes, gzip'ed storage) can be used.

You often want to scan across a few hours or days worth of logs, and indexes are poor for this purpose as the large number of disk seeks can kill performance. And full table scans of years of logs is not all that much fun either. For this, table partioning (supported in MySQL 5.1) is very useful. Store each day or week of logs in a separate table partition, partitioned on day (maybe use the 5.1 event sceduler, or simply a cron job, to add new partitions automatically). Then …

Just a quick note while it is in my head:

When testing software, especially for performance, remember to test on the same hardware you intend to deploy on. One trend I have seen for some time is that testing is often done on whatever happens to be lying around and not otherwise assigned.

While you can certainly test MySQL or an email server on such hardware, you should at least be aware that by providing hardware that does not match what you use in production you will not get an accurate picture, and sometimes the differences can be dramatic. This is especially true of enterprise software with high demands for memory and storage such as databases and MTAs: running such software on an old desktop or five-year-old server will not provide an accurate picture of how the software will perform on a multicore server with several gigabytes of RAM and a RAID array.

My advice is this: if you know you will be deploying the solution …

A client informed me of TheDailyWTF and I've subsequently read it quite frequently. It often features bad SQL of all dialects. It also features stupid business rules, management blunders, bad authentication, bad validation, race conditions and dodgy error messages. You'll certainly remember what not to do and when the forum comments aren't sarcastic and purile then you'll also gain insight into good practice.

If you're completing the survey then please mention your MySQL experience :-)

Jonathan Zuck has written an incendiary piece on the death of the peaceful compromise between free sourcerors and open sourcerors. The great divider? GPLv3.

I found the article profoundly fascinating, as it was evidence of intelligence gone awry. It completely misses the point that GPLv3 is just a license, and only applies to code to which it is newly licensed.

Will Linux suddenly be consumed by the dreaded v3? Nope. Will MySQL? Nope. JBoss? Nope. And so on.

The GPLv3 will only apply if these project maintainers choose to apply it to their code, and there's not a big line waiting for it.

Linus Torvalds says:

(The GPL 3) no longer works in the "fairness" sense. It's purely a firebrand, and only good for the extremist policies of the FSF. It's no longer a nice balance that a lot of people …

Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?

I have to disagree with the hard-won wisdom Joseph documents in this blog entry. Altough I 100% support his idea of version control for config files. Both his "Use mapfiles" and "Avoid generating configuration files" are 2 practices that I have to disagree with.

Don't use map files, use actual subdirs and map multiple trees on top of eachother, Create trees such as ALL , HOSTNAME , HOSTNAME-X , etc and layer them on top of eachother for the final filesystem. ALL is written first, then hostname and finally hostname-x, so if your CLUSTERNODE13 has a specific config file /etc/murphy that differs from all other nodes it sits in overrides/CLUSTERNODE13/etc/murphy. In the original sugestion with map files.. how would you map different content based on a server to the same filenames ? You'd end up getting a big bunch of …