I see my collague Anders wrote some thoughts on date handling in MySQL. I recently had to explain this really down to fine details for a customer, so Anders, everyone else, here's my conclusions...

All the fine details of allowed ways to enter dates are explained here:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

With dates, just as in many other cases, MySQL is very flexible in how you can input data. But one thing is that, between the multitudes of formats that are allowed, MySQL does \*not\* by default allow you to enter a non-existant date, e.g. "31st of February 2009". I know some other DB products allow that - they then store it as a correct date automatically (3rd of March 2009). This is mentioned at the end of …

Have you prepared yourself?

mysql> select 1234567890 - unix_timestamp();
+-------------------------------+
| 1234567890 - unix_timestamp() |
+-------------------------------+
|                         25091 | 
+-------------------------------+

Not much longer now!

One of the most common patterns I see in my consulting work is identifiers that are generated by MD5() or UUID(). Many times this is done in an application framework or something similar — not software the client has written. From the application programmer’s point of view, it’s just an incredibly handy idiom: generate a unique value and use it, you’re done.

Those values tend to appear in session identifiers, but that’s not the only place; I especially notice them in apps that use Java’s Hibernate interfaces, whether session IDs are involved or not. They propagate themselves all around the other tables, where they become secondary indexes and even get combined with other columns to make even bigger keys.

What’s wrong with this? There are two major things that hurt performance in such cases: larger data and indexes, and non-sequential values. I’ll ignore the latter in this article, since whether an identifier is …

It’s been a while since I’ve sent any news about the MySQL Cacti Templates project I started a while ago. Here’s what’s new:

First of all, it’s not just MySQL templates anymore. It’s a generic framework and tools for creating templates, and I’ve written templates for Apache, Memcached, etc. I just haven’t publicized [...]

Peter Gulutzan and Mark Leith have both written about the new PERFORMANCE_SCHEMA in MySQL. I’ve read through the worklog, or most of it — there were some spots where Firefox seemed to start overlaying parts with other parts, quite weird. But anyway I’ve read as much as I can.

Obviously many people have been [...]

So far this year I’ve been totally silent about the MySQL Conference and Expo 2009. In the past I’ve been a vocal advocate of going to the conference and sending your employees to the conference. So my silence was conspicuous to me, if not to you. I’ve always considered myself a strong [...]

We (Percona) just announced our Percona Performance Conference, and I wanted to tell you a little more about what we hope to accomplish with this conference. Let me show you some simple math that anyone can do.

There’s a handy iCal download of the conference schedule on the conference website. iCal is a plain [...]

Most software changelogs list what’s changed and what bugs are fixed during each upgrade. But when you’re evaluating an upgrade, what you’re typically concerned about is slightly different — it’s a combination of what’s changed and what might have broken. The fixed bugs aren’t as important for most people, who are either using [...]

As Hubert writes, PostgreSQL 8.4 has windowing functions.

Well done. I’ve been watching progress on this for a while. It greatly enhances the expressiveness of SQL.

What about common table expressions (WITH and WITH RECURSIVE)? Yes, PostgreSQL 8.4 will have common table expressions (WITH and WITH RECURSIVE) as well. That’s the “other” quantum [...]

As a long time DBA and Database Architect this idea is repugnant – make a database 100% de-normalized; one table and except for the one query, retrieval by primary key, nothing else works.  And yet we have had great success using this kind of database.

This does not replace the original normalized database, rather it is more like a permeant cache fed from the main database.  It is a MySQL database which has certain advantages over Memcached or other true caches such as it is permanent until our processes replace it.

Consider what it might take to build a simple web page: get a request, process it which might take many queries and some significant processing then send back your html.

…