Is mysql actually better?
By Justin Silverton
Mysql is the most popular open source database server right now. It is used by large enteprise level companies and small, single websites. There are countless numbers of books on the subject and many people claim it is better than the rest (microsoft, postgres)…but is it actually better? My goal here is to find out, based on Internet research, if mysql actually does outperform the other commercial and open source database systems.
Mysql 5.0 vs. Microsoft SQL 2005
Features
- Mysql 5.X now offers support for cursors, complete views, and stored procedures. However, Foreign Key support is still in its early stages.
- SQL 2005 has native support for xml, multi-dimensional data querying, and Visual Studio .net integration.
Cost
- Microsoft provides a free license for development use. $1400 for a commercial license.
- Mysql is free ($0) for commercial and non-commerical use. It is also possible to purchase a commerical license (to get around the GPL license) for $400.
Performance
- Mysql: MyISAM database table type uses less space and memory. Innodb and NDB clusters also now use 20% less space (new to 5.0).
- SQL 2005: needs more disk storage and memory requirements.
Replication
- Mysql: One way replication using a binary log, which can easily be replicated to multile machines.
- SQL 2005: Multiple forms of replication (snapshot,transactional, and merge), which are more complex and offers a greater degrees of flexibility.
Recovery
- SQL 2005: Very robust. There are multiple failsafes in place to prevent data loss. New features in this version also allow rapid restoration and data protection.
- Mysql: Falls very short in this respect. An unexpected shutdown of your server can cause data loss.
Reference: http://www.tometasoftware.com/MySQL-5-vs-Microsoft-SQL-Server-2005.asp
Mysql 5.x vs. Postgres 8.x
Postgres and mysql are both open source and supported by a community of users. Postgres is a little more difficult to find information about (most statistics are from older versions of postgres).
Postgres Advantages
- Stored procedures, Triggers, and a large set of built-in functions (including many functions for date manipulation) (many more features than mysql).
- Procedural language is pretty easy to learn if you know Oracle’s PL/SQL.
Postgres Disadvantages
- Even though it has more features, because it is not as popular or mainstreamed as mysql, postgres is at a disadvantage.
Some Links on the subject (some have comparisons with older versions of postgres/mysql):
- http://www.wlug.org.nz/PostgresVsMysql (chart detailing supported features)
- http://www.databasejournal.com/features/mysql/article.php/3288951
- http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html
Conclusion
Even though many commercial databases have more features than mysql, I still feel that it is a viable solution for most web applications and sites. Most scalability issues can be solved using a caching soluton called memcached (http://www.danga.com/memcached/). Future versions of mysql should concentrate on improving Data protection and recovery.
Also, because of it’s large acceptance on the Internet and in many web communities, it makes it a more practical solution than other open source databases such as postgres.
14 Comments so far
Leave a reply
Postgres is fairly mainstream, and has been well before (by almost a decade) MySQL was a twinkle in someones eye. Very popular in GIS, Scientific, Financial and Research fields.
But due to its design it never hit it off with the “more power” crowd, its like the difference between driving a Volvo - Postgresql, and driving a blown Mustang - MySql.
Postgres is designed around being very ACID compliant, you can put data into it and be VERY sure it is there, which means that it is also a little slower on inserts and updates, mostly due to wrapping each data change with a transaction.
Where as MySql was never designed to be ACID compliant, at least not as a front running concept, it was meant to be driven hard, and if the data is wrong, well, atleast you can get a whole lot of it really fast.
So it comes down, not so much to performance, but to the intended usage of the application the database would be supporting.
I would expect my bank, stockbroker or accountant to be using Postgresql (or Oracle), because my CASH is something that had better be done right, I don’t care if it takes milliseconds longer, just that its correct.
While my news from /. or DZone, well I don’t care if its right, mostly because the updates don’t contain anything of significance, so pure throughput works great here.
So, no, MySql ISN’T better, but, it’s also not Worse, its a tool in a toolbox, use the one that works best for you.
For a good 70% of my own applications I use Postgres, the rest are MySql.
Professionally, we have about 60% Postgres, 10% Oracle, 15% MySql, and the remainder made up of MSSql and a few less known DBs that are there purely because the client still uses them and hasn’t changed in a couple decades.
But we also make a lot of apps that the data just needs to be right.
[…] Mysql is the most popular open source database server right now. It is used by large enteprise level companies and small, single websites. There are countless numbers of books on the subject and many people claim it is better than the rest (microsoft, postgres)… but is it actually better? […]
under Performance you list only disk space and memory requirements? what about ACTUAL performance, doing large complicated queries on large databases? MySQL cant hold a light to MSSQL and certainly not Oracle, using MySQL for large enterprise applications would be laughable.
Your question is better when put in context. Is MySQL better than MSSQL for small web apps? prorbably? is MySQL the go to DB for enterprise needs? absolutly not.
“is MySQL the go to DB for enterprise needs? absolutly not.”
Damn. Such absolutes. I am 1000 years old! I say it, it’s true! Now shut up.
Well…
Doesn’t Yahoo use MySQL as their db? I’m pretty sure. Flickr definitely does.
Guess they’re not “enterprise” enough… heh.
There are also countless other examples (such as slashdot and livejournal).
Feature-wise, I would have to say that mysql is lacking in may respects, but it has been proven to handle large, enterprise-level websites.
Sure, some major websites are using MySQL. But that doesn’t mean its good, nor its working very well compared to other DBs.
It’s like comparing cars, they are all workable, they all can take you from one point to another. But the difference is performance, durability, mileage, etc.
I think no one cares when MySQL abruptly crashes one moment, and a backup kicks in in few minutes. It just means more work and acceptance.
“Sure, some major websites are using MySQL. But that doesn’t mean its good, nor its working very well compared to other DBs.”
If not working well compared to other DBs, then why are those other sites using them in the first place? Large companies that use mysql can afford other DBs such as Oracle or Microsoft.
“It’s like comparing cars, they are all workable, they all can take you from one point to another. But the difference is performance, durability, mileage, etc.”
Here is what I think is important to people deciding on a database: price, support, performance, and scalability.
Mysql doesn’t work for all situations, however.
Sure, they can afford, but why would they want to spend the extra money if they can go for a free option? It’s cost-cutting, going for a product that does its job - may it be short or long term, higher maintainance or lower, etc. Everyone likes things for free, and heck, that’s where people usually get deceived by its shortcomings.
Sure, price, support, performance and scalability are important decision factors. The more important question is, what are its priorities? Price? Or performance? Or is it long term scalability? How about long term cost and maintainance? Or maybe expertise? Or ease of use in support? Priority is utmost importance.
I agree that MySQL doesn’t work in all situations.
“Sure, they can afford, but why would they want to spend the extra money if they can go for a free option? It’s cost-cutting, going for a product that does its job - may it be short or long term, higher maintainance or lower, etc. Everyone likes things for free, and heck, that’s where people usually get deceived by its shortcomings.”
If something is free and complete garbage, people won’t use it for very long (and the word will get out that it’s not a very good application).
There are many free applications that just aren’t that good (oscommerce comes to mind). I tried to use it at one point in time and I quickly realized it just wasn’t up to par.
High-profile sites like slashdot, livejournal, and even parts of yahoo are using mysql as their back-end. This is proof that it can work on large-scale websites and is a “good” database server.
MS SQL vs MySQL…
I got into a debate with Paul with what started as a what coding language is “better” and it turned into…
While I won’t deny that MySQL has some high-profile sites using it, I still don’t buy your argument. Postgres’s user base is still large enough to provide gobs of community support. The advantages I see that MySQL has over Postgres are:
* Capability of switching storage engines from InnoDB to MyISAM, sacrificing ACID integrity for raw speed.
* Master-slave replication is used much more than in a Postgres environment. Postgres replication just doesn’t have the same community around it.
* Query caching reduces the need for add-in components such as memcached or PHP object caches.
Behaviors in transactions are also polar opposites. Postgres loves being inside a transaction, but MySQL performance suffers in a transaction. That alone can dictate different use cases for them (one for heavy nearly-read-only use, one for heavy data modification).
Personally, I enjoy working with Postgres because of the array of features it offers and the overall data integrity. If i put a set of rules on a table (via trigger functions, check-constraints, and custom domains), I can be damn sure that there isn’t any data in there that’s been messed up. I would be horrified to see a varchar field silently truncate extra characters on an insert.
Regarding comments about free (as in beer) software versus commercial software: If you have IT staff or in-house developers, no software you use is free. Everything has some sort of support cost. I’ve been in situations where I would have loved to just buy a solution that was either easy to use and maintain (saving money in the long run). It’s similar to the Photoshop vs. Gimp argument. Sure, the Gimp technically does all sorts of things that Photoshop does as well, but if it takes more time to create art assets, then it’s costing money to pay the artist.
Please don’t forget that youtube is ran on MySQL, and some of GOOGLES smaller apps uses MySQL as well but their main search facility uses Oracle.
Before I forget, GOOGLE caches all entries into memory for ultra quick search results. Oracle is just a background thing
I find this writeup and the subsequent posts very interesting. I gather that most writers are from the US and Europe. I’m on SQL and in Africa I can tell you for a fact that the licensing is killing as I speak I’ve been tasked to oversee a migration to MySql and the driving force is financial. Business overheads here are high enough. That said, I think I’ll feel confortable knowing that my database is safe after the power surge (very frequent here as well) Hope to hear your comments