MySQL database 

What is MySQL

MySQL (pronounced My-ess-queue-ell) is an open source, fee database server that is well suited to many web or intranet based data driven applications. 

Advantages

The two main advantages of MySQL is that it is free and it is fast. The latter is very useful for medium volume web or intranet applications or for installation on slower (ie older) servers. MySQL is fairly easy to use and has a moderately complete and standards compliant SQL langauge. (There is not a single database on the market which can claim 100% SQL standards compliance - a testimony to the complexity of database systems?).

MySQL is also platform independant - versions are available for Windows (most versions), Linux, Mac and Unix. Coupled with a platform independant application environment (PHP, for example), you can build true server independant web or intranet applications.

Limitations

MySQL has two major limitations compared to many other database systems. These may or may not be serious, depending on your requirements.

• MySQL has no foreign keys and no referential integrity. (excuse the jargon). This means that the system does not ensure that items in a second table referred to in one table actually exist in the second table. Most databases enforce this requirement, but MySQL does not. That means the application must enforce the integrity.
• MySQL cannot perform transactions. 'Transactions', in this context, mean things like debiting one account and crediting another - two steps which must be either succeed or fail as a whole. Partial success of such a transaction would corrupt the database and potentially cost a company business. Again, MySQL based applications must enforce transactional behaviour if required.

This means the MySQL is not well suited to on-line transactional systems, but does not present a problem with many web applications - in which the data is either non-transactional or changes infrequentkly, but is read frequently. Applications like on-line catalogs, discussion groups and intranet document management systems all fall into this category.

According to the MySQL developers, this was a design decision for MySQL - speed over complexity.

MySQL provides only rudimentary administration tools, but there are several good administration applications available on the Internet.

Installing MySQL

Full instructions for downloading MySQL are available at the MySQL web site. Compiled binary versions are available for most operating systems. Simply download the binary and install it. The installation method will depend on the operating system. 

The Windows download is approximately 13MB (excluding documentation ansd additional tools). Unzip the downloaded file and execute setup.exe. On Windows NT and Windows 2000, MySQL installs itself as a service. 

Using MySQL

Administration

As mentioned above, MySQL comes with only rudimentary administration tools. The only way to create a table, for example is to write and execute SQL commands in the mySQL command line application. However, there are a number of good free administration applications available on the Internet. I like MYSQL- Front, available from http://www.anse.de/mysqlfront/. There are also commercial front end applications available.

Applications

MySQL can be used for platform specific applications on Windows using MyODBC - ODBC drivers for MySQL available from the MySQL web site. MyODBC is a seprate download, it is not included with MySQL - available at the MYSQL web site. With MyODBC, simply connect to MySQL via an ODBC data source name and write the application in the normal way.

For a truely platform independant web or intranet application, you can use PHP. PHP includes a powerful and easy to use MySQL interface

Technical support

MySQL AB offers a range of commercial technical support contracts. For more information, go to the MySQL web site. If you prefer assistance in South Africa in Rands, rather than Dollars, drop me a line at jonathan@intexnet.co.za.

Conclusions

MySQL is a useful alternative to commercial web or intranet database applications. MSDE is also free, but is not as fast and is Windows specific. If speed and platform independance are important and the application does not require the use of complex transactions then MySQL should be high on your short list.   

Please feel free to drop me a line at jonathan@intexnet.co.za if you wish for more information or assistance with MySQL based applications or developments.