MySQL limitations

Prepare yourself to overcome or avoid this list of MySQL shortcomings from the book "The Definitive Guide to MySQL 5".

Limitations of MySQL

Many of the shortcomings listed in this section can be found on the to-do list of the team of MySQL developers, or have already been implemented.

Tip:The documentation for MySQL is not at all silent on the subject of shortcomings or missing features. There is a quite readable section in the MySQL documentation on the topic "How standards-compatible is MySQL?" There you will find extensive information on the points at which MySQL fails to comply with current standards. Often, a reason for the shortcoming is provided, and sometimes as well some pointers on how to get around the difficulty:

  • When MySQL is used with standard tables (table type MyISAM), then locking, that is, the temporary blocking of access to or alteration of database information, is in operation only for entire tables (table locking). You can circumvent the table-locking problem by implementing transaction-capable table formats, such as InnoDB, that support row locking.
  • In using MyISAM tables, MySQL is not able to execute hot backups, which are backups during operation without blocking the tables with locks. Here again, the solution is InnoDB, though here the hot backup function is available only in the form of a commercial supplement.
  • Many database systems offer the possibility of defining custom data types. MySQL does not support such functionality, nor is any currently planned.
  • MySQL has up to now ignored the general XML trend. It is not clear when MySQL will support direct processing of XML data. Numerous commercial database systems offer considerably more functionality in this area, and even the SQL:2003 standard provides for a host of XML functions.
  • MySQL is in fact a very fast database system, but it is very limited in its usability for real-time applications, and it offers no OLAP functions. OLAP stands for online analytical processing, and refers to special methods for the management and analysis of multidimensional data. OLAP-capable database systems are often called data warehouses.
  • MySQL supports, since version 5.0, stored procedures and triggers, but these functions have not yet fully matured (this applies especially to triggers) and do not yet have the same stability and plenitude of functions offered by commercial database systems.
  • Similar restrictions hold as well for the GIS functions introduced in version 4.1. Commercial database systems offer in some cases considerably greater functionality.

Chapter table of contents: What is MySQL?

Part 1: Database glossary 
Part 2: MySQL features 
Part 3: MySQL limitations
Part 4: MySQL version numbers 
Part 5: MySQL licensing 
Part 6: MySQL version names 
Part 7: MySQL alternatives

The above tip is excerpted from from Chapter 1, "What is MySQL?" of The Definitive Guide to MySQL 5 by Michael Kofler, courtesy of Apress. Find it helpful? Purchase the book here.

About the author: Michael Kofler holds a Ph.D. in computer science from Graz Technical University. He has written a number of successful computer books on topics such as Visual Basic, Visual Basic .NET, and Linux. Michael is the author of The Definitive Guide to MySQL 5, Third Edition and Definitive Guide to Excel VBA, Second Edition from Apress.

Dig Deeper on Database software management