DatabaseMySQL Considerations

MySQL Considerations

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

What is MySQL?

The MySQL database is an extremely fast, stable open source database with over 4 million installations around the world. In fact it is the most popular open source database in the world. One major point makes it unlike other open source databases, MySQL has a corporate side (MySQL AB). MySQL AB is a working corporation founded in 1995 it continues to develop upgrades to the software along with the open source community. Through its corporate structure and partner network, MySQL offers training, consulting and support for its users. Major corporations such as Yahoo!, Cisco, NASA, Lucent Technologies, Motorola, Google, Silicon Graphics, HP, Xerox and Sony Pictures rely on MySQL for heavy-duty, mission-critical applications. You will find MySQL predominantly used in the L.A.M.P. (Linux Apache MySQL PHP Perl) model.

Why use MySQL?

Many people look to MySQL as a low cost alternative to other databases. There are literally hundreds of tools and applications that work with MySQL many of them at low or no cost to the consumer. A commercial license of the MySQL server costs $440 (at the time of this article), while other databases can cost as much as $50,000 for a single processor server. There are many situations when purchasing a license is not required. If you never distribute (internally or externally) the MySQL Software in any way, you are free to use it for powering your application, whether your application is under GPL or other OSI. After using the product most users continue to use it over the long term. Now more than ever we are seeing large applications ported to MySQL from other databases. The most popular reason cited is to save money on licensing fees.

How good is it?

Usually you rate a database on size, speed and stability. How large a MySQL database can be is hard to say. I have dealt with several applications that ran < 100 GB. Mytrix Inc. for example, is maybe the largest MySQL database site in the world, storing more than a terabyte of data. As for speed and stability I must defer to a February 2002 database benchmark test performed by Ziff Davis Media Inc. Ziff Davis is the company behind PC Magazine, eWeek and other well-known publications. They said "the MySQL database server stood out as a winner. The MySQL server is presented as having the overall best performance and scalability along with Oracle9i." The article also had a couple of good quotes about MySQL: “Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems.” “The Oracle and MySQL drivers had the best combination of a complete JDBC feature set and stability.” “SQL Server and MySQL were the easiest to tune, and Oracle9i was the most difficult because it has so many separate memory caches that can be adjusted.” You can find out more at: http://www.eweek.com/article2/0,3959,293,00.asp

Will it work with my project?

The database itself supports a broad subset of the ANSI SQL 99 syntax, along with extra extensions such as the REPLACE statement and the LIMIT clause for SELECT and DELETE. In other words, it adheres to a standard and has other additonal functions to help the programmer. LIMIT is one of these functions. It has the ability to only pull a number of results that you specify. For example:

mysql> SELECT * FROM City LIMIT 10;

will only pull 10 rows from City as opposed to pulling all records without the limit function. Alternative syntaxes from other database systems are also supported, to make porting applications easier.

Even though larger databases use MySQL, most people use it for the backend of their website. It runs on many platforms Linux, Microsoft Windows, FreeBSD, Sun Solaris, IBM’s AIX, Mac OS X, HP-UX, AIX, QNX, Novell NetWare, SCO OpenUnix, SGI Irix, and Dec OSF. You can connect to a MySQL database server from all of the major platforms, using nearly any programming language including the ODBC interface.

MySQL database server’s unique independent storage engines let you choose the type of database storage (table type) that is most appropriate for your particular needs. If you need row-level locking and transaction support, you can use the InnoDB storage engine. If your application doesn’t require transactions, you can use the MyISAM storage engine for maximum performance. Using the InnoDB or Berkeley DB (BDB) storage engines, the MySQL database server supports transactions. The InnoDB storage engine also supports foreign key constraints.

The MySQL database server has an advanced permissions and security system, including support for SSL transport-layer encryption. As of version 4.0, the security system also allows you to limit server resources on a per-user basis. Version 4.0 of the server includes a new query cache, which can significantly increase the performance of commonly-issued queries, without requiring any special programming. Using database replication, you can have many “slave” servers running off a single “master” server for robustness and speed.

Full-text indexes allow you to search fields containing arbitrary text for specific words and phrases, including relevance rankings. The feature includes exact phrase matching and Boolean search operators, which allows for even more fine-grained control over your search results.

MySQL is used in applications and electronics devices, without end-users having any awareness of the underlying database. The embedded MySQL database is ideal for use behind the scenes in Internet appliances, public kiosks, turn-key hardware/software combination units, high performance Internet servers and self-contained databases distributed on CD-ROM.

Shortcomings

The only real drawback to using MySQL is if you may need a feature (such as stored procedures) that is very new or not yet available. MySQL is spending a lot of their efforts on adding new and additional features. Stored procedures are now available in version 5. Even though this version is not considered production, keep this in mind, MySQL does not release a version with any known “bugs”. It is only after one year with no known issues does it consider a release out of beta. In the end, the only way this works is that it is good solid code. Over the years I have dealt with many software vendors that put out a product too soon. MySQL has gone to great lengths to keep their product solid. Wouldn’t it be nice if all software vendors took a similar approach.

Conclusions

Downside, MySQL lacks some features that other databases have. Upside, it has a very low cost of entry and is similar enough to other databases that programmers and DBA’s can get up to speed soon. It has evolved into a solid contender with many of the features you find in fully commercial databases, without compromising speed and stability.

About the Author

John W. Horn PhD has been programming since 1983 and is currently the CEO of Interstate Software the only MySQL training center in North America. His company is the only “Gold Level” MySQL training, support and consulting group in the world. He is based in Kansas City Missouri.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories