Following comparison of world's two most popular database management systems (DBMS) is based on their use as a database for making web based applications.
Now that MySQL is officially a part of Sun, i would any day prefer MySQL for making my web based applications than any other huge (read heavyweight) databases, which probably offers so many features that are basically not needed for creating such applications.
Read Jonathan Schwartz's Blog to find more.
As regards Comparison, i would like to highlight following points:
Firstly being Open Source, MySQL comes as a free download for developers worldwide. On the other hand, for using Oracle, you need a license (and for that matter a pretty costly license)
With Google, Facebook and Sina.com prefering MySQL for creating network services, one cannot doubt over MySQL capability of handling high volumes of data (perhaps in terabytes or even more)
MySQL is already known for its faster data retrieval, and impressive clustering and replication features and with MySQL 5.0 supporting stored procedures, views and triggers, the situation has only improved.
If you are a newbie to this world of Databases, i would recommend you to start with MySQL and then gradually shift to some other, if need be.
Its not that Oracle is not useful or because i'm a SUN CA, I will continue to praise Sun's technology and products. 
The truth remains that Oracle provides a huge array of features as compared to any other existing DBMS, let alone MySQL. Oracle is considered as a giant in the world of database installations.
For these reasons, it is heavyweight and installation and uninstallation can get a bit tricky for novices.
In a nutshell, features is surely one area where MySQL loses to Oracle, but then MySQL is devoid of various disadvantages of Oracle.
Also Oracle 9i and 10g both can also be used a ORDBMS(Object Relational Database Management Systems) and supports ORSQL which only a few other databases like Informix and IBM's DB2 does, but MySQL cannot be used to implement a ORDBMS design.
To summarize, i would say that In the world of databases, there is no "one size fits all".
If you need the Oracle features, MySQL isn't going to work for you.
Conversely, if you don't need the Oracle features(which is most often the case), MySQL is a lot cheaper,
even with the license and the support contract.
And with Sun acquiring it, the situation is only going to improve.
Top Exec Speaks Up
Mr. Charles Phillips, president of Oracle, once made a statement at some conference about the comparison between Oracle and MySQL: "We're both in the transportation business," Mr Phillips said. "We have a 747, and they have a Toyota."
Mr. Marten Mickos, CEO of MySQL is more than happy with the comparison made, since there are many more Toyotas sold than 747s. "Toyota is a very profitable company," he added.
Mr Mickos maintained that the company is still more interested in new web-based application customers than it is in replacing existing database installations.
(taken from http://www.cbronline.com/article_news.asp?guid=9231B8BD-3788-4DB2-B85F-707E75857B58 )
Disclaimer:
The above comparison is based on my personal experience with both DBMS. If you find some missing/wrong information, please leave a comment.
I am not saying there is anything wrong with your comparison. It is a A vs B comparison, and things like DB2 are barely mentioned. What I find sad is that postgresql is so often kept out of the picture and people only compare commercial (typically oracle) to mysql.
Posted by Marc on February 29, 2008 at 10:33 PM IST #
Hi Marc,
Thanks for your comments. Well, i did not took PostgreSQL, DB2, Informix into account as i'm alien to them. Probably because they are not used that much in college campuses, at least in India. Well, you can read http://www.databasejournal.com/features/mysql/article.php/3288951 for comparison between PostgreSQL and MySQL.
Regards,
Agraj
Posted by Agraj on February 29, 2008 at 11:37 PM IST #
Hello,
thanks for the answer. Indeed you phrased it better than me: I find it sad that only oracle and mysql are present in college campuses in India (and in many other places). Which does not mean they are not great DBs and worth comparing.
Regards.
Posted by Marc on March 03, 2008 at 06:27 PM IST #
I have worked with both Oracle and mysql for over seven years now. I am a developer. I guess it all depends on how your programming habits are and the application you're building. The price is of least concern to me. I develop under de .NET platform using both oracle and mysql as a backend. (for different applications) I think Oracle is a very good product, but frankly I don't like their marketing techniques... it seemes all they care is money instead offering a good product. MySQL on the other hand is not taken seriously mainly because it's so cheap. But considering it's the database of choice of FaceBook, YouTube and Wikipedia, it's impossible to not to think of it as an extremely serious database.
MySQL 5.1 over Oracle10g:
Very resource friendly. you can have more connected clients on a single MySQL server than on a single oracle server running the same application.
Pluggable Storage engines for different purposes; MyISAM for fast Reading, InnoDB for transaction safety, Archive for storing massvie amounts of data, Memory for session variables, NDB Cluster for fast processing and FILE (a third party plugin) for direct access to file repositories (like images and attachments); pluggable thrid-party storage engines (Like KickFire and memcache).
The primary key auto_numbering; you don't need a separate trigger and squence and you can use the SELECT @lastid:=last_insert_id() query safely.
Better suport for batched statements that allow less trips to the server. For example INSERT INTO employee (name) VALUES ('Rubén Hernandez'); SELECT @employeeid:=last_insert_id(); INSERT INTO users (employeeid) VALUES (@employeeid);
Bulked prepared Statements from strings (PREPARE stmtToRun FROM @sqlString; EXECUTE stmtToRun... Again... reduces trips to the server.
Real database separation (not like Oracle schemas wich are really only different users with different privileges )
The ability to store tables on files (even mysql configuration) for exact mirror backup. (This can be actually very quick if you use a RAID 1 Configuration. This is how it works... Flush the table writes to disk and stop synchronizing the backup disk, (usually a third disk) and you have an instant backup. A good hardware Raid can even eliminate processor and filesystem overhead... Backups under 50ms.. Anyone??.
The ability to use unsigned integers of various sizes instead of the Oracle number field wich greately reduce size of the table greately reducing I/O time.
The Federated Storage Engine, wich allows to have many tables on many different servers behaving as one. (I have tested a project with scanned documents of 75 TB and sub-seocnd response times from 800 concurrent clients)
Oracle 10g over MySql 5.0:
Great PL/SQL Support for handling logic inside the database (very useful if you like doing logic on the database level... not so useful if you don't use this approach)
Better Trigger Capabilities: Multiple triggers for single event, and groupped trigger suport (DELETE OR INSERT OR UPDATE in a single trigger).
Better transaction support. Oracle can insert and update more transactions per second than MySQL/InnoDB. Only useful if you need transactions. Most of the time I rely on my application for checking that the query is correct so I don't regulary put the server trough the transaction overheading.
Conclusion:
In general I believe Oracle has a better PL/SQL scripting wich is good sometimes. however, I think most of these features should be on the application layer of the solution, not the database level. As a database per se.. I belive Mysql is better. I find the plugabble tables extremely efficient.
Posted by Rubén Hernández Isla on October 15, 2008 at 01:10 PM IST #
Hey Ruben,
Thanks for the wonderful comparison....Couldn't have been better !!
Posted by Agraj on February 24, 2009 at 01:50 PM IST #