Browse DevX
Sign up for e-mail newsletters from DevX


Using MySQL 4.0 with .NET : Page 5

MySQL isn't just for Linux. And it has a lot of the same great features that you love SQL Server for. Find out what this open source database has to offer.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 Limitations
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 parameter—birthdates 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.

MySQL Performance
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:


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.

Rod Paddock is the editor of CoDe Magazine. He has been a software developer for more than 10 years and has worked with numerous tools including Visual Studio .NET, SQL Server, Visual Basic, Visual FoxPro, Delphi, and numerous others. Rod is president of Dash Point Software, Inc., an award-winning software development firm that specializes in developing applications for small to large businesses. Dash Point has delivered applications for several corporations including: Six Flags, First Premier Bank, Intel, Microsoft, and the US Coast Guard.Rod is also VP of Development for SQL Server tools maker, Red Matrix Technologies. You can contact Rod at rpaddock@dashpoint.com.
Thanks for your registration, follow us on our social networks to keep up-to-date