| MySQL-Oracle comparison by Baron Schwartz (http://www.xaprb.com/blog/2009/03/13/50-things-to-know-before-migrating-oracle-to-mysql/) 1. Subqueries are poorly optimized. 2. Complex queries are a weak point. 3. The query executioner (aka query optimizer / planner) is less sophisticated. 4. Performance tuning and metrics capabilities are limited. 5. There is limited ability to audit. 6. Security is unsophisticated, even crude. There are no groups or roles, no ability to deny a privilege (you can only grant privileges). A user who logs in with the same username and password from different network addresses may be treated as a completely separate user. There is no built-in encryption comparable to Oracle. 7. Authentication is built-in. There is no LDAP, Active Directory, or other external authentication capability. 8. Clustering is not what you think it is. 9. Stored procedures and triggers are limited. 10. Vertical scalability is poor. 11. There is zero MPP support. 12. SMP is supported, but MySQL doesn’t scale well to more than 4 or 8 cores/CPUs. 13. There is no fractional-second storage type for times, dates, or intervals. 14. The language used to write stored procedures, triggers, scheduled events, and stored functions is very limited. 15. There is no roll-back recovery. There is only roll-forward recovery. 16. There is no support for snapshots. 17. There is no support for database links. There is something called the Federated storage engine that acts as a relay by passing queries along to a table on a remote server, but it is crude and buggy. 18. Data integrity checking is very weak, and even basic integrity constraints cannot always be enforced. 19. There are very few optimizer hints to tune query execution plans. 20. There is only one type of join plan: nested-loop. There are no sort-merge joins or hash joins. 21. Most queries can use only a single index per table; some multi-index query plans exist in certain cases, but the cost is usually underestimated by the query optimizer, and they are often slower than a table scan. 22. There are no bitmap indexes. Each storage engine supports different types of indexes. Most engines support B-Tree indexes. 23. There are fewer and less sophisticated tools for administration. 24. There is no IDE and debugger that approaches the level of sophistication you may be accustomed to. You’ll probably be writing your stored procedures in a text editor and debugging them by adding statements that insert rows into a table called debug_log. 25. Each table can have a different storage backend (â€storage engineâ€). 26. Each storage engine can have widely varying behavior, features, and properties. 27. Foreign keys are not supported in most storage engines. 28. The default storage engine is non-transactional and corrupts easily. 29. Oracle owns InnoDB, the most advanced and popular storage engine. 30. Certain types of execution plans are only supported in some storage engines. Certain types of COUNT() queries execute instantly in some storage engines and slowly in others. 31. Execution plans are not cached globally, only per-connection. 32. Full-text search is limited and only available for non-transactional storage backends. Ditto for GIS/spatial types and queries. 33. There are no resource controls. A completely unprivileged user can effortlessly run the server out of memory and crash it, or use up all CPU resources. 34. There are no integrated or add-on business intelligence, OLAP cube, etc packages. 35. There is nothing analogous to Grid Control. 36. There is nothing even remotely like RAC. If you are asking “How do I build RAC with MySQL,†you are asking the wrong question. 37. There are no user-defined types or domains. 38. The number of joins per query is limited to 61. 39. MySQL supports a smaller subset of SQL syntax. There are no recursive queries, common table expressions, or windowing functions. There are a few extensions to SQL that are somewhat analogous to MERGE and similar features, but are very simplistic in comparison. 40. There are no functional columns (e.g. a column whose value is calculated as an expression). 41. You cannot create an index on an expression, you can only index columns. 42. There are no materialized views. 43. The statistics vary between storage engines and regardless of the storage engine, are limited to simple cardinality and rows-in-a-range. In other words, statistics on data distribution are limited. There is not much control over updating of statistics. 44. There is no built-in promotion or failover mechanism. 45. Replication is asynchronous and has many limitations and edge cases. For example, it is single-threaded, so a powerful slave can find it hard to replicate fast enough to keep up with a less powerful master. 46. Cluster is not what you think it is. Maybe I already said that, but it bears repeating. 47. The data dictionary (INFORMATION_SCHEMA) is limited and very slow (it can easily crash a busy server). 48. There is no online ALTER TABLE. 49. There are no sequences. 50. DDL such as ALTER TABLE or CREATE TABLE is non-transactional. It commits open transactions and cannot be rolled back or crash-recovered. Schema is stored in the filesystem independently of the storage engine. -- Xarpb Comments by Shawn Green, MySQL support engineer (28 Jan 2025) MySQL was never designed to be a clone of Oracle (the database). We have distinct differences in design and implementation that make us a wonderful product to use as a "general purpose database" ... If your project cannot possibly function without one or more of the features that MySQL does not provide, then don't use it. However, our feature set has been and continues to be complete and powerful enough to be the storage engine behind some of the internet's most popular and heavily visited websites. I can see reasons why some of the feature differences (aka overhead) may be useful in certain use cases. However there is a long history of popular usage that indicates that not everyone, or every project, requires the full set of features you describe: http://www.mysql.com/customers/ I agree with some of the points you make and we are working to implement some of the features you mentioned. On the other hand some of those "deficiencies" that you mention are specific strengths of the MySQL system: 23. There are fewer and less sophisticated tools for administration. MySQL doesn't need them. That alone should tell you something about our reliability. 24. There is no IDE and debugger that approaches the level of sophistication you may be accustomed to. You’ll probably be writing your stored procedures in a text editor and debugging them by adding statements that insert rows into a table called debug_log. Again, this is an indication that you don't *need* complex tools or a GUI to work with MySQL. The simple solution is often the better solution. It also allows you to develop for your server from practically anywhere, not just a machine where your GUI tools are installed. 25. Each table can have a different storage backend (â€storage engineâ€). Yes, we absolutely allow this. Each engine brings a certain strength to the storage and retrieval solutions you can create with MySQL. We explicitly recognize that there is no "one size fits all" approach that meets the needs of every problem. This also allows for special-purpose solutions to be integrated into MySQL: http://solutions.mysql.com/solutions/?type=29 28. The default storage engine is non-transactional and corrupts easily. True: MyISAM is does not require the disk and CPU overhead of tracking changes transactionally. False: In my experience (I do work for Support) MyISAM is rarely corrupted. I dispute this claim. 29. Oracle owns InnoDB, the most advanced and popular storage engine. As of yesterday, this became a moot point. Oracle now owns MySQL, too. http://www.oracle.com/us/sun/index.htm 34. There are no integrated or add-on business intelligence, OLAP cube, etc packages. False. Please see: http://solutions.mysql.com/solutions/ 38. The number of joins per query is limited to 61. True, but why is this a problem? Do you frequently (or ever) need to join more than 61 tables into the same query? If you do, I propose that you need to revisit your schema design choices or review how you write your queries. In this case, I think we are discouraging bad practices. 39. MySQL supports a smaller subset of SQL syntax. There are no recursive queries, common table expressions, or windowing functions. There are a few extensions to SQL that are somewhat analogous to MERGE and similar features, but are very simplistic in comparison. Again, the vast majority of data storage and retrieval activities do not require these features. If you absolutely cannot function without them, then do not use MySQL. 44. There is no built-in promotion or failover mechanism. Again, we have no "one size fits all" approach to this. We do not assume to understand your business processes nor do we want you to design your process to support our procedures. The failover process is yours to design and implement as you see fit. 45. Replication is asynchronous and has many limitations and edge cases. For example, it is single-threaded, so a powerful slave can find it hard to replicate fast enough to keep up with a less powerful master. Yes, it is asynchronous. This is a distinct advantage to many read-heavy applications and it allows MySQL to scale out better than most, if not all, other RDBMS systems. http://www.mysql.com/why-mysql/white-papers/mysql_wp_scaleout.php http://www.mysql.com/why-mysql/scaleout/booking.html 49. There are no sequences. Please explain why auto_increment cannot meet this same need? Why have the overhead of two ways of performing essentially the same function? This is just one less way to confuse your design. Also see http://mysql-dba-journey.blogspot.com/2009/06/converting-oracle-schema-to-mysql.html |