I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …

I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …

Someone at work pointed out a rather interesting blog entry that purports to dump on MySQL Partitioning in a rather big way.In my opinion, he raises one valid point, but as for the rest of what he says, he's either misguided or misinformed. Except where he's Just Plain Wrong. Why? Let me give some reasons... He seems to be confusing Partitioning with Cluster. NDB Cluster does use partitioning (and has done so since before it was part of MySQL), but in 5.1 it's possible to use partitioning with most other storage engines, no Cluster necessary. (This is what's actually "new" about it in 5.1.) Whether he's talking about Cluster or about Partitioning (it's not clear which is the case), his contention that every box ... contains the whole database is simply wrong. He is correct in saying that you can't add or drop Cluster nodes online. This is being worked on. I can't say whether it'll make it into 5.2 (I personally doubt it, but I could be wrong about …

Someone at work pointed out a rather interesting blog entry that purports to dump on MySQL Partitioning in a rather big way.In my opinion, he raises one valid point, but as for the rest of what he says, he's either misguided or misinformed. Except where he's Just Plain Wrong. Why? Let me give some reasons... He seems to be confusing Partitioning with Cluster. NDB Cluster does use partitioning (and has done so since before it was part of MySQL), but in 5.1 it's possible to use partitioning with most other storage engines, no Cluster necessary. (This is what's actually "new" about it in 5.1.) Whether he's talking about Cluster or about Partitioning (it's not clear which is the case), his contention that every box ... contains the whole database is simply wrong. He is correct in saying that you can't add or drop Cluster nodes online. This is being worked on. I can't say whether it'll make it into 5.2 (I personally doubt it, but I could be wrong about …

(The following applies to Apache 1.3. I've no idea whether it's still true in Apache 2 or not. Some of you will probably react to that news like I do to someone who's still not upgraded to MySQL 5.0, but there you are.) Ever been in a situation where you want all the files in a directory listed, even if one of them's named index.html (or whatever default you've specified as the default DirectoryIndex)? I banged my head against my monitor several times before I finally figured this one out. It's one of those things that's apparently too obvious to be mentioned in the Apache documentation, so I'll put it here: AllowOverride Indexes Options +Indexes DirectoryIndex /

(The following applies to Apache 1.3. I've no idea whether it's still true in Apache 2 or not. Some of you will probably react to that news like I do to someone who's still not upgraded to MySQL 5.0, but there you are.) Ever been in a situation where you want all the files in a directory listed, even if one of them's named index.html (or whatever default you've specified as the default DirectoryIndex)? I banged my head against my monitor several times before I finally figured this one out. It's one of those things that's apparently too obvious to be mentioned in the Apache documentation, so I'll put it here: AllowOverride Indexes Options +Indexes DirectoryIndex /

A friend recently gave me a used copy of O'Reilly's Learning the bash Shell, 2nd ed. which I've been perusing a bit here and there, not really with the objective of learning all there is to know about bash in one swell foop, but rather to have bits of it lurking about in the back of my head for future reference. One of those bits came in handy a couple of days ago. As part of my job, I need to compile new builds of MySQL from the sources pretty frequently, and when I file a bug report, I like to be able to include the date the version I used was built. I mean, it's not like I can't do something like ls -l ~/bin/mysql/bin/mysql | cut -d' ' -f7 to get the file creation date for the client binary, but I thought it would be cool to have it in the MySQL version string so I didn't have to exit mysql or switch to another shell. I already knew that MySQL can be built using a --with-server-suffix option that adds an arbitrary string to the output of …

A friend recently gave me a used copy of O'Reilly's Learning the bash Shell, 2nd ed. which I've been perusing a bit here and there, not really with the objective of learning all there is to know about bash in one swell foop, but rather to have bits of it lurking about in the back of my head for future reference. One of those bits came in handy a couple of days ago. As part of my job, I need to compile new builds of MySQL from the sources pretty frequently, and when I file a bug report, I like to be able to include the date the version I used was built. I mean, it's not like I can't do something like ls -l ~/bin/mysql/bin/mysql | cut -d' ' -f7 to get the file creation date for the client binary, but I thought it would be cool to have it in the MySQL version string so I didn't have to exit mysql or switch to another shell. I already knew that MySQL can be built using a --with-server-suffix option that adds an arbitrary string to the output of …

My friend Ron from the Brisbane MySQL Meetup is cooking up a new project that uses MySQL's new Sakila sample database. It's a tag-based Java/JSP framework called HTMLtags. If you're into Java, you might want to check this out. If you like it - and especially if you'd like to lend a hand with development - drop Ron a line. I'm sure he'd appreciate it! (I'd volunteer, but what I don't know about Java would fill a library.) BTW, Ron and I, along with Australia's newest MySQL AB employee, are also starting to cook up a new Open-Source-y Something, but it's still a secret for now.