MySQL, MSSql, Oracle: When to use which?

Question

• What's the limitation?
• Is there a specific volume of data each can handle regardless of disk space?
• When to use what assuming licensing is not a problem?

Answer 1

As to skaffman's point you may want to have a look at postgres if mysql isn't scaling for you. It is a more mature and robust than mysql and is opensource. The time to make the switch is highly dependent on you application environment, however, if you need clustering and replication to work properly 100% of the time then postgres will not let you down (as mysql has for me in the past)

Answer 2

It depends what you are trying to do and obviously it has to do with cost.

• MySQL and Postgres are very widely used by a huge number of startups because its open source and there is a lot of support out there for people using it
• MSSQL is good if you are using MS programming languages because of the ease to connect and use.
• I have never used oracle but know people use it a lot for data warehouseing so can't have that much of a bad name

All of these will suffer from similar issues when scaling because they are RDBMS databases. They do also have decent ways to get round it and with a decent ORM used in your code then it shouldn't matter what you use.

Pick the one that all the developers are comfortable with

Answer 3

This is a very nuanced question that really cannot be easily answered, as each situation can provide many pluses and minuses. Also, MySQL being owned by Oracle now and several branches off of the main functionality means that MySQL != MySQL anymore.

If you are looking for really really big data sets, then you will like have to break with the RDBMS sets and start to look at things like MapReduce and other large data set processing technologies.

I have personally worked with all three over the past decade or so from the application perspective. They all have their advantages, like MSSQL working will with the other Microsoft technologies like LINQ where as MySQL having a large open community support and Oracle being the workhorse of the commercial sector with lots of ability to embed application logic right into the database.

Again, it really depends on the application, the situation, the skills of the people who will maintain it after it is developed, commercial considerations, hardware and platform considerations, etc etc etc.

Answer 4

It would help narrow things a great deal if you'd provide details like whether or not you intend to distribute the database along with your software; your system will be hosted; how much data; etc.

Don't assume anything with regard to licensing. Get a lawyer, maybe even one who specializes in open source law.

"...regardless of disk space..." - capacity always depends on this. Where do you think the data goes? Better to think about things like sharding your data, RAID, clustering, replication, etc.

I would worry about any system whose developer had to come to a forum like this to ask that kind of question. You should have people on staff with sufficient skill and knowledge to have a strong opinion on this sort of thing.

Answer 5

Perhaps one variable which people overlook in these cases is the availability of expert support. Okay, so currently there's an oversupply of people who can help you with db issues, efficiency issues, disaster recovery etc. However this may not be always the case in the future, and it's the applications you use it for that may be the defining issue. Are there people in your organization who have experience in one or more of the relevant databases? (as it happens I believe that someone's who's become proficient in say Oracle, can become fairly competent in Sql*Server or Mysql in a fairly short space of time) You state it's going to be used for your financial systems - perhaps you really need input from a consultant who's worked on implementing and/or supporting financial systems - for example I understand that Sybase is popular in City type firms. Or perhaps there's an off-the-shelf package that utilises a preferred database? Try and define exactly what your system(s) needs to do first.

Answer 6

I'd say if you want to compare apples to apples, then it is MySQL vs SQL Express, vs Oracle Express.

Or if you have $, then it is the MySQL support license, MS-SQL Standard, vs whatever Oracle's cheapest offering is.

In my experience, once you choose a language, e.g. Php goes best with MySQL, then you've chosen your DB. Java goes well with Oracle. C# goes well with MSSQL.

Similarly, if you choose your OS, then unix flavors run MySQL or Oracle, but MSSQL is windows only. MySQL and Oracle work on both unix and windows of course.

If you need to buy many machines, then not having to pay OS licenses for the server helps in scaling.

Answer 7

Is the application buy or build ?

• If Buy, does it support all three and talk to the app vendor about the differences ?
• If Build, then is it an in-house build or contract out. If contracting out, put out your requirements and let the suppliers put their arguments.
• If in house build, then first look at why you are not contracting out. Normally it is because you already have an in house capability, so look at that expertise.

You want some sizing information first. Are you talking data volumes in megabytes, gigabytes or terabytes ? What are your uptime requirements, backup (recovery time / recovery point) ? How much concurrent activity ? Is that peak ?

Generally any database system is fine for data storage and retrieval. High-end analysis, load balancing, replication, management, backup/recovery, auditability, security are all areas you may need to consider.