SQL Review - MySQL

Featuring:MySQL's Development Cycle

MySQL may be the epitome of Open Source development - "no code before its time". Developers have heard word of MySQL 4.0 since 2000 and have actually seen the first alpha code starting in October 2001. As of August 2003 the first release of the full product was achieved and and in late Decmber the 4.017 release was made which eliminated/squished all of the bugs exposed in the system by Rationing's analysis engine. The Rationing analysis turned up .06 bugs per thousand lines of code where as the industry average is .57 bugs per thousand lines of code. A most enviable record for any commercial or open source not just for clean code but responsiveness in delivering fixes and patches to recently released code. There have been 17 revisions since August and users can climb aboard and update however they find convenient. There are complete build and update advisories for each edition.

MySQL - Recent Development History

Major database vendors are quick to point out the departures from SQL 99 standards made by MySQL. They point to the lack of Unions and a full set of joins. Then they go on to cite the lack of a full range of sub-queries. And finally, I have heard a saleman's pitch concluding "its a miracle developers can work done with MySQL". Well lets take a look at what is in MySQL just to relieve our saleman's concerns.
In its 4.0x version(current production), MySQL has added support for UNION, multiple tableDeletes, and full text search functions. All of these are welcome additions with full text search being the hidden jewel. Just as important 4.0 MySQL adds a number of key performance enhancements including query caching (very important for query speed up), improved replication code methods, and better full-text indexing which compliments the full text search functions just added. Finally, 4.0 MySQL add more security and control capabilities particularly through the GRANT and REVOKE command reportoire.

4.1x MySQL which is in beta, adds the important enhancement of subqueries and the creations of unnamed views . From an operational point of view, 4.1 MySQL adds full Unicode support including the ability to define character sets for columns, tables, and databases. From the performance standpoint 4.0 MySQL adds many more improvements to indexing and b-tree algorithms. Specifically, there is a btree index on heap tables, a new key cache for MyISAM tables, and better optimization of parameters for prepare statements and new multiple statement handling in the C API.

But perhaps the sleeper in 4.1x MySQL is the addition of OpenGIS spatial variables and classes to the language. These extensions currently only work with ISAM tables; however the implementation is quite general supporting Geometry, Point, LineString, Curve, Surface, Polygon, and a set of GeometryCollection classes.The full set of spatial functions are available in interactive programs such as mysql or MySQLCC. This is impressive support of an otherwise highend/extra cost feature of Oracle and other databases.

The 5.x Features

When MySQL completes and releases the 5.x version of its product it will have matched and in some cases surpassed the major databases in basic SQL compliance and functionality. MySQL 5.0 is in advanced alpha testing. The three key add ons to 5.0 are stored procedures using the SQL 2003 standard syntax, cursors and a complete set of views. The performance enhancement include better support for in MEMORY tables plus added indexing options for user control. as well 5.0 will settle VARCHAR implementation differences, in fact moving closer to the defato standards.

5.1 MySQL rounds out the features list with a complete implementation of foreign keys, adding of triggers to the stored procedure begun in 5.0, adding complete outer join plus constraints to the DDL. However, it is important to note that code for 5.1 is not availablem and 5.o is still alpha. MySQL has a lot on its plate in rationalizing the many component databases in its product suite - InnoDb, ISAM, the new data clusters, and MaxDB. This will prove even more challenging as developers need more cross database services such as replication, journaling and recovery, parallel processing, and advanced in memory and clustering with large scale databases. And in these circumstances, MySQL is carrying along 4 basic database architectures. Yet despite these difficulties, MySQL is still positioned oon price-performance to pick-up huge maounts of development apps - simply because the 3rd party tools are improving, PHP 5 is a major advancement, and most important of all - it has the Redmond magic formula of lowest ciost while being "just good enough".


MySQL has a very ambitious schedule for getting its high price-performance databases up to enterprise standards. Note I did say databases. But MySQL's price-performance advantage may well hit a wall at the topend as more advanced computing through Web Services, distributed systems, and asynchronous processing in SOA and BPM workflows just demands a much higher standard of database engine. MySQL will be able to compete on the low to medium scale end with not only low prices but also growing ubiquity of its engine - My SQL is rapidly replacing Access in that role. And this replacement of Access is not based on price alone. The runtime performance, availability of third party applications and tools, plus MySQL's cross platform reach make it highly attractive to frontline developers. Why Star Office/OpenOffice went with Software AG for their database offering is really unfathomable.

In sum, MySQL will continue to prosper because it is rapidly rounding out its full distributed processing feature set. It also continues to be offered as a default component in many applications and development toolsets. And its price performance will constantly get it on RFP qualifying lists. This is not a bad thing because in healthcare, customer services, manfacturing processes and many other organizational arenas there are scores of database applications that don't get built because of the runtime per seat costs - leave alone the per seat development costs. So MySQL may be opening up a new tier of database applications. Hopefully so.

Top of Page  Tutorials Home 
©Imagenation 2001-2004