A couple more MySQL notes I wanted to write down before I forgot.

There's a MySQL Perl module called DBIx-DWIW that looks interesting. The name stands for "DataBase Interface stuff - Do What I Want". Normally, you have to jump through some small hoops to get stuff back from a MySQL query in Perl. You do stuff like set up the connection, set up your query, prepare the statement for execution, excute it and fetch rows within a loop or something, handling each record returned individually. If you just want one row from a DB it's a pain.

Enter DBIx-DWIW. To get a single record, you set up the DB connection like before and then you have something like my @prez = $db->Array("select * from presidents where lname='Kennedy'"); and then you'd have an array called @prez with all JFK's stats. A clever fella can trim the complexity down even further by sticking that "$db->Array" call as the parameter for a foreach loop, or taking an array slice from it directly.

I'm going to take a more in-depth look at it next week.

Second note: I wrote small tool last Thursday that backs up MySQL databases. It works over a network and can back up all the databases on the server, individual databases, etc. It's basically a wrapper around mysqldump, but it has a little smarts thrown in. It store MD5 checksums of the dumped data, for example, and only saves dumps if the contents of the database have changed. It zips up dumped files to save space. And it can keep incremental backup files if needed. This makes it easy to do things like backup the DB, do some work of unknown usefulness/quality, do more backups, and roll back to a know state if you decide the changes aren't what you wanted. You could also just watch your database grow, I guess. It seemed like a useful feature to add in and it was simple to do.

I might make a few additions to it. I'd like to not use mysqldump. Then I could do things like back up only the DBs that the supplied username can see (right now it errors out if you try to back up stuff you aren't allowed to read). If I can keep from shelling out to another program I'd be happy, even thought it's a perfectly reasonable thing to do.

Third note: If you want to see what your MySQL server can do on your hardware, check out SuperSmack. It basically whollops your server and gives you an idea of what's going on.

Fouth and last note: Go and read all about the query cache feature introduced in MySQL 4.0.1. It stores SELECT statements and their results. When a query comes that matches a previous one, the cached result is sent out instead of the statement being re-run and a new result generated. You get things like a minimum speed boost of 238% and 20,000+ queries per second on old (900MHz P3) hardware.

Of course none of the two people that read this site (Hi, Tess!) care about this stuff, but it's as good a place to make notes as any other... :-)