I wrote before about a generic, cross-platform way to simulate the SQL ROW_NUMBER() function in any RDBMS. There is a much more efficient way to do this on MySQL with user variables. Background Please see my previous article on how to simulate the ROW_NUMBER() function for the background. I’ll use the same table structure and data in this article. Unfortunately, that’s a quadratic algorithm, so it’s not something I’d do much (though I once did it over small sets of data in SQL Server 2000 at a jobsite).

All this time I was under the impression that boatloads of spam in our inboxes help promote porn but now I learn that hard drives promote porn.  Huh?  Well, I guess by that same logic the driveshaft in my car promotes car accidents.  Don't believe me?  Take the driveshaft out of your car and see how many car accidents that car is involved in.

It's really sad that we've come to a point where noone is responsible for themselves.  Everybody wants to be on Oprah and blame the neighbor, the dog, the parents, and the teacher.  Anyone but themselves.

And now it is apparently not your fault if you watch porn.  After all, the monitor just kept showing it long after you wanted to stop watching.  That damn hard drive just kept holding on to it long after you wanted …

It's not often that I get to pull Knuth's The Art of Computer Programming down off the shelf and immediately solve a problem with it. Today, I did.

A big "Thank you!" goes to my parents for giving it to me long ago.

The problem is that for the way almost all computers work, floating-point numbers do not follow the associative property. This results in wrong results and a good bit of hilarity.

I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause.

So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can't be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.

If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on …

This is a reminder to anyone who works in technology, particularly open source, that the job market is healthy. If you've been waiting for the right time to start looking, that time is here.

I'm just saying this because I get a continuous stream of requests from friends, former co-workers, open source community associates, and random people who read here. Things like "Mike, we desperately need a MySQL DBA. Do you know anyone?" or "Do you know anyone who is available to do some PHP/Perl work." My unscientific sense is that companies are paying more because good open source folks are in demand.

Rather than posting every individual job that comes along I'm just going to say it once in general...look around. You'll find them. These companies are out there actively looking, they aren't the very special, hidden, must-be-part-of-a-secret-society jobs. You should be able to find them with a little poking around.

Just a …

Adam Machanic has published on SQLblog.com the 21st edition of Log Buffer, the weekly, human-edited review of database blogs. You can publish an edition of Log Buffer too. It’s good fun and an excellent way to promote your own blog — The Pythian Group Blog and Log Buffer are widely-read and highly-rated. Read the Log [...]

Laurentiu Cristofor has an excellent blog post, Who needs encryption?, which presents some point blank facts about encryption and the correlations you can draw from those facts. This post isn't a SQL Server or even a database specific post. It's about encryption in general.

I love his Fact #1: Encryption does not eliminate the need to protect some data. I was recently talking with some peers about whole disk encryption technologies. The idea behind whole disk encryption technologies is if someone were able to steal the hard drive (such as by taking a laptop), as long as the hard drive was powered off, by powering it on they wouldn't immediately get access to to the data. They would have to decrypt the hard drive. Well, there's two ways to go about …

I recently reset replication on one of my servers and since then my relay_log_space_limit=8G setting has stopped working since the relay log files continue to grow beyond the 8G limit (currently 15G).

Has anyone else experienced this before? Why would it suddenly stop honoring the relay log space limit?

Today the Apache OFBIZ project, for which I'm one of the committers and core developers, was approved to graduate from Apache incubation. It has been almost exactly a year since David Welton from the Apache Foundation first talked to me about OFBiz joining the Apache Software Foundation. At the time it almost seemed so far off and in the distance, but now that it's close at hand, I'm going to jot down a few notes about it.

What is OFBIZ?

For those of you who don't know, Apache OFBiz is a community-developed open source project for building business software: everything from high end ecommerce sites to Point Of Sales to document management to open source ERP and CRM systems, to less common things like …

For all you SNMP afficianados, a database logging SNMP client has never been easier.

The Cacti Virtual Appliance is running Cacti in just a couple of minutes with a fully automated setup, onboard MySQL and phpMyAdmin.

The Cacti Virtual Appliances comes outfitted with about 1.7 GB of diskspace, but can automatically use Virtual Hard Disks so there is no limit to how much SNMP data can be stored.

To learn more about Cacti, visit the Cacti Homepage.
Download the nano sized (40MB) virtual computer image from our downloads page.