Features of MySQL
Just because MySQL is basically free (see sidebar on page 1) does not mean that it has a fundamentally limited feature set. MySQL is an excellent database for mid-range web database applications. Existing installations currently handle dozens of databases, thousands of tables, millions of records, and tens of gigabytes of data, which covers the majority of web applications.
In all honesty, it is missing a few significant features compared to its high-end competitors (discussed in the next section), but the original design goal of producing a fast, robust, and easy to use database has governed the decisions about what features to include and omit. More importantly, users have a strong voice in which features get added to MySQL, as well as the ability to add the functionality themselves.
MySQL was designed from the ground up as a multi-threaded database engine, an excellent architecture for both multi-user environments (like the web) and multiprocessor servers. The database engine is written in C and C++. Database files use B-tree disk tables with index compression and are accessed using ISAM (indexed sequential access method). While these features need not directly concern you, they add up to very fast database performance as well as a high degree of reliability.
For the hacker, its good to know that MySQL is developed using GNU Automake, Autoconf, and libtool for portability. The source, a C compiler, these tools, and the desire to add a feature is all you need to customize MySQL to your own specifications. Extensive version histories and planned enhancements are available at the MySQL web site.
Regardless of how good the guts of the database are, the quality of the SQL implementation is what's really important. While there are a series of SQL standards, database vendors tend to pick and chose a subset of the functionality in the standard when they implement it. Vendors also typically add their own extensions specific to the needs of their users.
The folks at TcX, in an effort to provide a fast and robust database, have focused on the core functionality used by the majority of users and added a few tricks of their own to speed up common tasks. Basically, MySQL offers the core functionality of ANSI SQL (SQL2) within certain limits:
- Standard database and table management functions (such as DROP, ALTER, CREATE).
- Core SQL verbs (SELECT, UPDATE, INSERT, DELETE).
- Wide range of data types (signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET and ENUM types).
- GROUP BY and ORDER BY clauses including group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX(), and MIN()).
- Built-in functions for SELECT and WHERE clauses.
- Fast inner joins.
- Left outer joins.
- Up to 16 indexes per table.
- Table and column aliasing (SQL92 standard).
- Multiple character sets for both data and sorting/comparison operators.
Most plain vanilla SQL should work fine in MySQL. Deeply nested clauses and esoteric functions are the most likely points of failure if you are porting SQL code from another database to MySQL. And, of course, proprietary extensions from other languages will fail. But in my experience, translating SQL from routine applications built in other database to MySQL is a fairly painless process.