Working from the legacy of mSQL, TcX decided MySQL had to be at least as fast as mSQL with a much greater feature set. At that time, mSQL defined good database performance, so TcX’s goal was no small task. MySQL’s specific design goals were speed, robustness, and ease of use. To get this sort of performance, TcX decided to make MySQL a multithreaded database engine. A multithreaded application performs many tasks at the same time as if multiple instances of that application were running simultaneously. Fortunately, multithreaded applications do not pay the very expensive cost of starting up new processes.
In being multithreaded, MySQL has many advantages. A separate thread handles each incoming connection with an extra thread that is always running to manage the connections. Multiple clients can perform read operations simultaneously without impacting one another. But write operations, to a degree that depends on the type of table in use, only hold up other clients that need access to the data being updated. While any thread is writing to a table, all other threads requesting access to that table simply wait until the table is free. Your client can perform any allowed operation without concern for other concurrent connections. The connection-managing thread prevents other threads from reading or writing to a table in the middle of an update.
Another advantage of this architecture is inherent to all multithreaded applications: even though the threads share the same process space, they execute individually. Because of this separation, multiprocessor machines can spread the threads across many CPUs as long as the host operating system supports multiple CPUs.
In addition to the performance gains introduced by multithreading, MySQL has a richer subset of SQL than mSQL. MySQL supports over a dozen data types and additionally supports SQL functions. Your application can access these functions through the American National Standards Institute (ANSI) SQL statements.
MySQL actually extends ANSI SQL with a few features. These features
include new functions (
IF, and others), the
ability to increment fields (
LAST_INSERT_ID), and case sensitivity.
Some SQL features found in the major database engines were omitted intentionally from MySQL. For the longest time, transaction support was the most notable omission. The latest releases of MySQL, however, provide support for transactions. Stored procedures, another notable omission, are scheduled for the 4.1 release that should be available at the same time as this book. Finally, MySQL does not support most additions to the SQL standard as of SQL3. The most important SQL3 feature missing from MySQL is support for object-oriented data types.
Since 1996, MySQL AB has been using MySQL internally in an environment with more than 40 databases containing 10,000 tables. Of these 10,000 tables, more than 500 contain over 7 million records—about 100 GB of data.