It’s New Year’s Eve, a date that should strike terror into the hearts of many, because tomorrow a bunch of their queries are going to fail.

Queries to “find all birthdays in the next week” and similar are always a nightmare to write. If you want to see a bunch of examples, go look at the user-contributed comments on the MySQL date and time function reference. This post is about a slightly saner way to do that. There’s still some nasty math involved, but a) a lot less of it, and b) at least the query will be able to use indexes[1].

So here’s my tip: instead of storing the user’s full birthdate, just store the month and day they were born. Try it. You’ll love it!

[1] Yes, I know Postgres can index a function. So this can be considered a jab at MySQL, which can’t.

Related posts:

1. …

If you’d like some improvements to the Cacti templates (MySQL, Apache, Memcached, and more) I’ve been maintaining, please make your voice heard — either write to the mailing list, or post a new issue (or comment on an existing one) on the issue list. I’ve got a bunch of work underway, including a test suite for ss_get_by_ssh.php.

Related posts:

1. Version 1.1.6 of Better Cacti Templates released I’ve
2. Version 1.1.2 of improved Cacti templates released I’ve
3. …

MySQL has a couple of sanity-check features to help keep you from doing dumb things.

• max_join_size is a configuration option for the mysqld server program. It throws an error if you write a query that the optimizer estimates will examine more than this number of rows.
• –safe-updates is a command-line option to the mysql client program. It throws an error if you write an UPDATE or DELETE without a) a WHERE clause that refers to an indexed column or b) a LIMIT clause. It also sets the max_join_size and select_limit variables.

The –safe-updates mysql client option actually sets three variables server-side. Let’s see the effects. First, the defaults:

Numeric types in MySQL have two varieties: - “precise” types such as INTEGER and DECIMAL; - the IEEE-standard floating point types FLOAT and DOUBLE. As a rule of thumb, the first group are for exact, “counted” quantities. The INTEGER types represent whole numbers, and DECIMAL represents “fixed point” decimal, with a preset number of places after the decimal point. Various widths of INTEGER are available in MySQL, from 8-bit TINYINT to 64-bit BIGINT. Calculations with integer types are fast, as they usually correspond to hardware register sizes. DECIMAL is commonly used for quantities like decimal currency where the number of digits of precision is known and fixed. For example, exactly counting pennies in two decimal digits. Computation with DECIMAL is slower than other types, but this is unlikely to impact most applications. In the other category are FLOAT and DOUBLE, which are the 32 and 64-bit IEEE standard types, which are usually …

I’m on the MySQL conference committee again this year, so I’ll be reading and voting on your session proposals. My past experience is that reading 400 session proposals is a mind-numbing job, and I will be optimizing it to save my time. That means I will be scanning your proposals and deciding very quickly how to vote. I wrote before about how to submit a great proposal, but I’ve refined my opinion since then. Here are my suggestions:

• Write for two audiences: reviewers and attendees. Both of us want the same thing: to decide as quickly as possible whether your session will be good.
• Write a strong title/headline. Don’t know how? …

The MySQL conference site for 2010 is live, and ready for your proposals. Submit! Submit! Submit!

Related posts:

1. There will be an O’Reilly MySQL Conference in April 2010 O’Re
2. How to write a good MySQL conference proposal I’m
3. Attending the IT Management Conference I’ll

Related posts brought to you by Yet Another Related Posts Plugin.

One of MySQL’s notable projects was splitting the product into two editions: Enterprise Edition and Community Edition. This move alienated many in the community, and failed to create meaningful differentiation on either side, even with a team of people beating the community bushes for “contributions.” The net differentiation was ultimately Jeremy Cole’s SHOW PROFILES functionality, which made Community better than Enterprise. Sun put less effort into making this split work, and eventually they abandoned it.

But that could change under Oracle’s stewardship. Oracle’s promises to maintain a GPL …

Pentaho Solutions

Pentaho Solutions, Business Intelligence and Data Warehousing with Pentaho and MySQL. By Roland Bouman and Jos van Dongen, Wiley 2009. Page count: about 570 pages. (Here’s a link to the publisher’s site.)

The book is big in part because it’s about a GUI tool, so there are the requisite number of screenshots (but not too many). It is structured into four parts, each on a different topic.

The first part is 4 chapters on getting started with Pentaho: from a quick-start through …

I’ve released version 1.1.5 of my improved Cacti templates for MySQL and other components of a LAMP application. This is a pure bug-fix release. One of the bug fixes prevents spikes in graphs, but requires you to rebuild your RRD files. There are upgrade instructions on the project wiki for this and all releases. Use the project issue tracker to view and report issues, and use the project mailing list to discuss the templates and scripts.

The full changelog follows:

2009-12-13: version 1.1.5

  * Support for getting slave lag via mk-heartbeat was broken (issue 87).
  * The …

As long as the whole world is chasing this meaningless “NoSQL” buzzword, we should recognize that InnoDB is usable as an embedded database without an SQL interface. Hence, it is as much of a NoSQL database as anything else labeled with that term. And I might add, it is fast, reliable, and extremely well-tested in the real world. How many NoSQL databases have protection against partial page writes, for example?

It so happens that you can slap an SQL front-end on it, if you want: MySQL.

Related posts:

1. On the unhelpfulness of NoSQL My favorit

2. A Review of Beginning Database Design by Clare Churcher

   Beginn

…