Hello everyone,

I'm currently working on a medium-sized website, including a store and 'regular' news / article / commenting features. The company me and my team are creating this site for also has a real-life store, which will have software running on it's consoles that queries the same database used by the website, for product information and such.

Up until now we've been developing the website using MySQL as a database solution, but recently the team that's developing the software for the real-life store has brought up the idea to switch to a database server called Firebird - http://firebird.sourceforge.net/
I couldn't find any benchmarks or featurelists on that website, nor could I find feature comparisons and benchmark-comparisons with Firebird on the MySQL site. Ofcourse I'm dying to know which database is the better choice. The team that's developing the software claims that Firebird is more reliable when there are a lot of users quering the database.

Could anyone tell me more about the cons and pros of Firebird in relation to MySQL? Thanks in advance!

p.s.: English is not my native language so I apologise for any errors

Here's a quick list of what I could come up with...

MySQL has a much larger community and support than Firebird has.

Firebird *terribly* lack good ODBC support... None of the drivers I've tried so far worked... And the ones that worked "best" were pretty expensive.

If you don't need transactions (e.g. just feed your website for display from MySQL), the only known database that is faster than MySQL is Oracle...

MySQL is supported on a zillion platforms.

MySQL has lots of documentation for PHP, ASP, etc support...

You'll find more people that know MySQL than Firebird specialists.

If you're doing lots of DELETE, performance on Firebird will degrade a lot faster than MySQL (see sweep operation).

BTW, I am not doing propaganda for MySQL, I'm a DB2 fan!

totally agree

and i would recommend mysql for all who ask me for an extremely fast, reliable, easy to manage, and most valuable: you can use it for FREE if you want.

Where MySQL wins: nice full text indexing, and, as others have pointed out, it's ubiquitous. It has a few SQL extensions that you'll wish had made it into the standard like "REPLACE INTO." It's nice to use a database that, if you type "show tables" will just show you a list of your tables.

Where it loses: it's unreliable. Not in the sense that it's liable to crash, but in the sense that it provides weak foreign key constraints, has no triggered procedures and transaction-safe tables are an extension.

That means your integrity checking has to be strewn all over your application. Not that redundant integrity checks shouldn't be in your application, but the DBMS should be the failsafe point. You should also be certain that data in the DBMS is correct, even if something goes wrong with the app.

Moreover, the fact is that most MySQL developers don't know how to write integrity checks properly. Let's face facts: these are people who are hacking together a website (that's what I use it for...) and probably wouldn't know of any monitors beyond the one on their desk. Structured exception handling... wow, that's really fun to write, and did you actually do test cases for it?

Proper exception handling requires a degree to understand let alone code, it's incredibly painful to code, and it protects against glitches that rarely happen. Even if you could, why on earth would you ever want to code it when you could let the DBMS take care of all that? You may as well just let the DBMS throw an error and pass it back to the user. Check the error codes for common problems and just tell them it's an unexpected error the rest of the time.

Speed: with MySQL the only way to get proper transaction handling is through InnoDB tables. (And these have all kinds of restrictions on them.) In a real life database, one to which you would actually entrust your valuable data, all those supposed speed benefits go up in smoke.

The only way to be able to do live backups is with InnoDB. Again, if you're doing a business website, live backups is not optional.

MySQL doesn't have stored views or sub-SELECTs. It won't, according to the authors, until someone writes it for them or pays them to do it. What does that mean for you? You get to write lots of iterating loops which will bloat your code making it buggier and harder to update.

Inconsistent table types: There are 6 table types, and the semantics are different for all of them. Example: foreign key constraints only work between InnoDB tables, and full-text indexing only works on MyISAM tables.

Stored procedures: They have to be written in C++. That means that all that iterating you have to do (because there are no sub-SELECTs or stored views) is going to result in repeated round-trips. You can kiss network transparency goodbye.

Anyway, as with any DBMS, there are plenty of minor issues and it carries all the baggage of SQL. These are the problems that prevent the use of MySQL in anything but quick and dirty hacks.

you're right, but all things always have pros and cons. no person is perfect, so does the software. if one wants Unix in nice GUI, it wont be that stable, same thing as with windows thats never been as reliable as Unix in stability.

thats why people want a football player with the brain of Zindane, the left leg of Roberto Carlos, right leg of Ronaldo, head of Keane, the (God) hand of ... Maradona.... and more!!!

but how?

Well, I have to disagree. I work for a company that processes in excess of $500 million annually and uses MySQL 3.23.5x (haven't upgraded all the servers to 4.0.x yet). We have never used transactions (found very little use for them actually). MySQL has never crashed on its own. We had 1 incident where the hard disk filled up due to other process logging and MySQL basically bombed.

As for live backups... we do it currently. A simplistic solution is to set up a Master and a Slave. Take the Slave down every now and then to move the data off site, start it back up and it gets all the data it missed from the master. If you do this once an hour, you lose an hour's worth of data at most.


Sub-selects are in version 4.0.x. Views will be in 5.x. Full foreign key support will be in 5.x

These are all overhead in my opinion. In 5 years of coding/designing transaction processing systems with MySQL, I've had very little need for sub-selects or views. I think these are over-used.

You can write UDF's in C. I saw a version someone hacked together to be able to write them in Perl. Not sure if that ever got developed further though. Probably just proof of concept stuff, but it worked within the limits of Perl 5 (threading is poor but is required for MySQL UDFs).

As for repeated round trips. Version 4.1.x supports prepared statements within the C API (not sure if Perl's DBD::mysql, or PHP supports these calls yet) . It also supports the sending/returning of multiple queries/result sets at one time.

Like I said, I use it at work for some pretty major processing. We have sub-systems that take over 3 million transactions daily. One system in particular takes 61 questions per second every single day.

Still running good.

Sub-selects are in 4.1, which is still in alpha, unlike 4.0 which is stable. (Again, this highlights the need for fact-checking when talking to MySQL fans!) And 5.0 is, at this point, vapor-ware.

As for your big system, true, you can still do massive systems that rely on '60s technology, just like they did in the '60s. Smart money says you'll upgrade to Oracle when upgrades start to come in late because your team is ferreting out every last integrity check that's strewn throughout the application code becomes prohibitive.

Umm, C is "60's technology." What do you think most other languages are built off of?

Besides I'm not against other databases. They have their uses. For what I do, MySQL works and it works well. You, however, sound fairly biased against it.

I just wanted to point out that your views of MySQL are not correct. It can, and does, work on a large scale. We've been running MySQL for 5 years in transaction processing with few problems. Maintenance is extremely simple. Plus, we don't have to pay the $1 million+ that Oracle would cost.

There are a few things I wish MySQL had, but nothing that can't be worked around with some good system design and ingenuity.

Yup. C is 60's technology, and that's a great analogy for describing the weaknesses of it. Think about all those problems you have to design around in C: buffer overruns, problems with void*, spaghetti coding. Sure, you can write great software in it and sometimes the more advanced stuff (e.g. Java) isn't up to task and you *have* to use it, but you shouldn't dismiss the weaknesses.

I'm biased against *all* SQL DBMS's. I want a true relational DBMS, and I'm quite happy to criticize vendors as long as they come up short. And, as you pointed out, they do listen. That's why, for example, the MySQL team is working on sub-selects and views, and has added transactions.

BTW... has anybody actually used Firebird or Innobase? Can the original poster (who seems to have gone AWOL) check the criticisms I made of MySQL and post his findings back to the board?

Hi all, thanks for all the replies, quite helpful.
I haven't gone AWOL, whatever that means, just been busy.

We still haven't decided what database to use, mostly because both teams would really like to stick with 'their' database, and none are really experts, which is why I turned to the users of this board, among other people.

My team would like to use MySQL, simply because we've never used Firebird and therefore feel more comfortable developing in a MySQL environment. The same probably goes for the other team and Firebird. However, if Firebird would indeed be the best choice, we'd ofcourse switch.

In this thread a lot has been said about MySQL, but not much has been said about Firebird. Could anyone give me some insight on how Firebird compares to MySQL?

None of the involved team are real database experts, so I thank you in advance for any further replies.

Absent Without Leave, used in a satirical manner.

Honestly, your best bet would be to google Firebird and read the documentation. Or check their mailing list.

If you find something interesting, do post it here.

please remember that this is mysql area, experts of one db have little chance to be experts of others, havent it? you should try firebird forums for more opinions

cheers

In case you haven't made a choice yet, I'll post my opinion.

I'm a firebird user -- I'm the DBA (when needed, which is not often) and the tech-lead for the team. I'll vouch for firebird in the places I've used it -- we access it from a fat client (c++/win32) at my main job, and I've used it with php several times for websites (in fact, helped report problems to the php team when ibase_* functions were having issues.)

It's reliable. It's stable. It's fast. It's got the features you will likely need. And it's generally "correct" in the logical sense: transactions aren't just a random feature. They're part of relational theory, when it comes to a multi-user database system. If it's interactive, if it's mission-critical data, if it's a multi-user system ... go with something designed for those. Postgresql, firebird, SapDB, ... find whatever you like. But please don't use mysql. (all three mentioned are free.)

(and yes, a web-app SHOULD use transactions to make sure semi-simultaneous page hits don't run into problems because of updates occurring somewhere in the database, whether it's from the web, or from an internal database feeding stuff to it. and a db like firebird where transactions are part of the very essence of the database system, not an add-on ... means that if your power fails, or something else goes wrong, your db will still be in a perfectly valid state, ready to go again. it's a bit like the difference between fat32 drives and ext3 or reiserfs -- don't you want your data to be safe?)

Well, MySQL handles transaction now with the InnoDb handler (which comes standard now). So there's not problem on that side and for read-only stuff, nothing is as fast as MyISAM tables (expect Oracle). The only thing missing now are views... But it's coming soon...

Ahem, transactions work *only* if all tables involved are InnoDB. Most of the other features only work on MyISAM tables. Since the only way to use MyISAM safely is with locks, the speed advantages are false.

And, please, I've heard enough promises about all the wonderful features that are "coming in the next release... we promise!" MySQL will offer views... but will they be crippled the way transactions are? What limits will there be on updatability? Will they only work on certain table types?

4.1 is still alpha, and you act like a 5.0 beta is out. I don't demand that everyone be a ruthless skeptic, but at the same time, some of you guys sound like PR flacks.