chir.ag/tech [archive]

 
 
 
 
 
 
 

/tech home / projects / personal 'blog / about chir.ag

 

ARCHIVE: MySQL: The first love you can't stop loving...

Thu. Sep 29th 2005, 08:41pm:

After my frivolous whining earlier this month about picking up PostgreSQL 8.x in favor of MySQL 4.x, I tried the newly released MySQL 5.0. I had been following the 5.x news for months now and was eager to try out the features like views, triggers, and stored procs. The release worked well on my test system (WinXP Pro) and I was glad to find out that the database did not feel bloated at all.

I'm sure there's a thousand sites out there already with performance specs etc. but having worked with databases for over a decade, I think one can just feel how fast/stable/bloated a database is without detailed benchmarking. MySQL 4.x had always given me the feeling that I was going 110mph on a Kawasaki and getting 60mpg but without wearing a helmet - thrilling and cheap but dangerous and lacking amenities. (Having lost data many times, I'm so cautious now that all my MySQL databases from four different servers are dumped on to multiple remote servers every few hours.)

MSSQL feels like a gas-guzzling SUV and Oracle reminds me of a 1940's German Military Tank. Refreshingly, MySQL 5.0 feels like a Honda Civic - has pretty good features, still good on mileage, and relatively stable. I have absolutely no doubt that everywhere I use MySQL 4.x currently, I'm gonna upgrade to 5.x gradually (assuming the data conversion/upgrade etc. is simple enough). I see no reason why I'd want to stick to 4.x on my webservers anymore. 5.x has views that I absolutely need! I'm tired of writing the same SQL JOIN queries over and over again or copy-pasting queries from MS Access and fudging them to work in PHP with MySQL. In addition to views, I'm pretty sure I will use stored procs, triggers, and the Federated Storage Engine.

The FSE is helpful when you want a local MySQL database to retrieve records from remote database. Currently for many databases, I make multiple MyODBC remote-link/connections in MS Access but that's a big security risk because the user can be anywhere and so the server must accept any remote host. Now I can setup MySQL Remote A to accept remote connections only from MySQL Local B and all internal users can connect to Local B. Since Local B is firewalled, Remote A is better off security-wise. So I think all in all, a very good upgrade and unless some stability issues come out in a few months, I'm definitely upgrading most of my systems.

However, you can't fly across the Atlantic in a Civic. And for that, I need PostgreSQL. PG8 is free, just like MySQL, so it's not going to cost my company anything extra for the new ERP system that I'm designing. I could go on and on about the random extra features PG8 offers me in comparison to any other database but the singlemost reason I chose PG8 is stability. MySQL 4.x has served me well for years but I cannot trust my own skills to administer data worth millions of dollars using it. And since MySQL 5.0 was just released, it is only natural that I wait for a few months before jumping in and using it for the ERP system. Yahoo has enough people with more skills than me to manage billions of records and transactions using MySQL but my few years of experience dealing with data corruption in tables with less than a million records in MySQL 4.x makes me think twice before I tell our CEO that I picked MySQL over everything out there for the singlemost important system that runs our business.

I see myself as a carpenter - there's things to be made and I'm gonna need some tools. There is a right tool for every job and MySQL happens to be the Philips Screwdriver while PostgreSQL is a wrench. Here's hoping that the screwdriver remains a screwdriver and the wrench remains a wrench.