Choosing the right database: MySQL vs. Everything else

Question

It would seem these days that everyone just goes with MySQL because that's just what everyone goes with. I'm working on a web application that will be handling a large quantity of incoming data and am wondering if I should "just go with MySQL" or if I should take a look at other open-source databases or even commercial databases?

Thanks, Ben

EDIT: Should have mentioned, am looking for optimal performance, integration with ruby + rails running on debian 5 and money is tight although if it will save money in the long run I would consider making an investment into something more expensive.

Answer 1

I think PostgreSQL is a very viable alternative to MySQL. It's much more Oracle-like.

Answer 2

Mysql is great, and mssql is great. I haven't used anything else. I would say if you are completely on the fence, go with the technology stack you are strongest with. I have a good amount of c#, asp.net, and other Microsoft stack experience, so it is pretty natural for me to specialize in mssql. If you are more familiar with *nix, php, etc, you may be more at home sticking to an open source stack. You can certainly mix and match the two stacks, but sticking to one world or the other can avoid some pain for you.

Answer 3

"It would seem these days that everyone just goes with MySQL because that's just what everyone goes with." If MySQL is the only thing that people use then why are Oracle and MSSQL still around?

The debate as to which database engine to go for can be talked about until the cows come home. I personally have always found one constant in choosing a databse engine. The one you can afford is generally the one you go for.

If you can justify spending XXX on a database then you probably know the reasons already for choosing it.

Answer 4

Well, there are may differences between the RDBMS of the world. Take a look at http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features

Using this as a guide you should be able to narrow down your choices a lot.

A few things to keep in mind:

SQL Server 2005 Express is limited to a 4GB file size, but has excellent support int the .NET and Java languages.

MySQL will run on both Windows and Linux, and many languages support it (including .NET and Java) with external libraries.

SQLite is supported by effectively every operating system, and can be distributed as an integrated part of your application.

Answer 5

Personally, I try to avoid MySQL whenever I can for the following reasons:

1. The default storage engine MyISAM lacks Foreign Key support. Innodb does, however it does not support Foreign Keys to MyISAM tables for obvious reasons.
2. If I attempt to insert invalid data, MySQL will happily change it for me.
   1. Illegal DateTime, Date, or Timestamp values are convert to "zero": http://dev.mysql.com/doc/refman/5.1/en/datetime.html
   2. Varchar and Char type columns: http://dev.mysql.com/doc/refman/5.1/en/char.html
   3. Numeric Datatypes depending on SQL Strict Mode: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

These things may not be of any importance to some people, but when it comes to quality of data, I would rather use something else.

Answer 6

Firebird the open sourced and branched version of Borland's Interbase is pretty good, it works happily on most (all?) flavours of Linux and is very performant. I am not a RoR guy so I don't know the details, but I do have a friend in NZ who uses Firebird with all his RoR projects, it definitely works with RoR, and works well.

Edit:
Found a link to a Firebird Rails Adapter here

Answer 7

Like duffymo I also recommend PostgreSQL. It is very nice from a developer perspective: work on many platforms (both unix-based and Windows), has vary stable interfaces to any laguage/environment I worked (Windows, Linux, Delphi, Java, Perl, Python). Stored procedure language: PLPGSQL is also easy and powerful. User support (newsgroups, lists, SO) is nice and helpful.

Answer 8

Please be a little wary of the idealistic points-of-view put forth by people who may have axes to grind. MySQL is a capable, robust and scalable solution for many problems, but this is usually not so right out-of-the-box as its defaults are extremely conservative. Like any database product, you will need to spend time tuning its installation for the desired performance. You will also need to spend some time accomodating its limitations, which is also true for any database you choose.

MySQL's popularity is partly self-generated: a lot of hosting providers provide it, so a lot of people use it.

Answer 9

I've posted this before, but I have noreason to change this advice:

MySQL is easier to start using.

Nicer UI tools. Faster, if you don't use ACID. More tolerant of invalid data. Autoincrement columns are as easy as typing autoincrement. Permissions aren't as tied to the file systems and OS users. Setting a delimiter is easier than using pg's "dollar sign quoting" when writing a stored proc. In MySQL, you connect to all databases, not just one at a time.

Posgtres is much more standards compliant, but it's uglier and more complicated, especially from a UI perspective. It used to require manual vacuuming, and actually enforces referential integrity (which is a great thing that can be a pain in the ass). Autoincrement is much more flexible, but requires sequences (which can me masked by using serial), and wait, what's an OID?

So if you don't really know or care much about databases, data validity, ACID compliance, etc, but you do care about ease and speed, you tend to go with MySQL.

Too many (not all, but many) "web programmers" know a lot about "web 2.0" or PHP or Java, but don't know much about database theory or practice ("an index? what's that?"). They tend to see a database as just a fancy hashtable or bag of data, and indeed one that's not anywhere as dynamically changeable or forgiving as a hashtable.

For these folks, MySQL -- because until 5.0 it wasn't really an RDBMS, and in many ways still is not -- is a godsend. It's "faster" than the competition, and doesn't "waste time" on "esoteric" database stuff a web programmer doesn't want, understand, or see the value of.

For somebody with a database background, on the other hand, MySQL is a minefield: stuff that should work (complicated views, group bys, order bys in group bys) may work or may if you're lucky crash the server, or if you're unlucky just give results with incorrect data.

I've spent days working around some of these things in admittedly complicated by not extraordinarily complex views and group bys.

And MySQL isn't really faster. If you're using InnoDb tables for ACID (or just because at more than 30 Million rows, MyIsam tables tend to get crappy), yes a straight one-table select is probably faster than in pg. But add in joins, and pg is suddenly significantly faster. (MySQL is especially bad at outer joins.)

In summary: if to you the database is a bag, if you never intend to do data mining or reporting, if you're mostly interested in serving up big hunks of text with few relations or updates -- that is, if you're using a database to power a blog, MySQL is a great choice.

But if you're actually managing data, if you understand that data lives longer and is more valuable to a business than front-end programs and middle-tier business rules , if you need the features of a real database, use pg.

A "web programmer" who has decided all his table structures can be auto-generated by Hibernate (or some other ORM) looks at that and says, "too complicated" and "I bet complicated means more cost and slower speed" and so he goes with MySQL.

As I said, pg is far superior, and I hate mucking with MySQL's bizarre bugs, and I think that overall pg performance is probably better than MySQL for any even slightly complicated query.

But MySQL makes things look (deceptively) simple, so you get a lot of people who don't really understand database design figuring that MySQL is a great choice.

Use pg. It's consistent, it's reliable, it's standards-compliant, it's faster on (even moderately) complicated queries, it doesn't completely throw off your schedule with weird bugs.