MySQL Limitations and Uniqueness
Now that you know how to install and access data from MySQL, you need to understand some of the limitations and unique features of MySQL.
MySQL doesn't support stored procedures. On the project teams I work with, we require that all data access must be performed using stored procedures. The MySQL.com Web site promises that MySQL version 5.0 will support stored procedures. (As stated earlier this article covers version 4.0).
MySQL version 4 also lacks support for triggers. A number of development shops would say that triggers are a critical feature. This is another feature slated for the MySQL 5.0 timeframe.
Finally, MySQL has no support for creating views. The MySQL Web site promises that this feature is slated for inclusion in the 5.1 release.
Unique and Cool MySQL Features
MySQL supports of a number of unique data types, or at least these data types are unique from a SQL Server developer's perspective. MySQL supports date and enum data types. The date type is useful for storing date data that does not need a time parameterbirthdates and anniversary dates come to mind. The enum data type gives developers the ability to control the contents of a data field via a list of valid values.
I think that it's also nice to see support in MySQL for replication, cross platform database deployment (Windows, Linux, SGI, HPUX, and others), full text indexing, searches, and many other features.
I did some rudimentary performance testing of MySQL and overall I am rather impressed. The machine I have MySQL installed on is a Pentium III running Windows 2000 with 128MB of RAM.
In the first part of my benchmark I loaded a small table with the first 95,000 words from a text file of Leo Tolstoy's War and Peace
, which I use for benchmark testing. Loading these records took approx 53 minutes. Not too shabby considering the hardware.
Using the data I loaded I began running some queries. One query I ran was a simple group, count, and sort by of each word in the table similar to what you see in the following code:
SELECT word, COUNT(*)
GROUP BY word
ORDER BY 2
The query returned 20,000 records grouped by word in approx 1.20 seconds. That's acceptable in my book. I performed numerous other queries like the one above and found that the performance was consistent across all queries. Not too bad on a machine with only 128MB of memory.
When I started this article I was very unfamiliar with MySQL. Like many people, I read about MySQL but wasn't sure how it might play a role in my development process. Now a short time later I find that MySQL is a nice database platform that I may recommend for some database applications to my clients. I say some
because MySQL, while robust, is missing some very critical features needed for real enterprise applications. By the time MySQL version 5.1 is available, the product may be a serious contender for the kind of enterprise applications I build. At the time I'm writing this article, MySQL version 4.1.0 is in alpha according to the MySQL Web site. There isn't a posted time frame (that I could see) for version 5.
I also want to say thank you to Ed Leafe for helping me find the tools to make this article happen.