Skip to Content

Experiences with MySQL 5.0 as a stand alone Relational Database in Ecological Studies

Printer-friendly versionPrinter-friendly version
Issue: 
Spring 2006

- Brian Riordan (BNZ)

Are you looking for a fast, reliable, easy-to-install and manage relational database? Do you need it to be free as well? Do you need tons of support options and training? Then MySQL 5.0 is the database for you. MySQL has all of the capabilities and then some to be the robust database you need for ecological applications and data.

The first MySQL database was developed May 23, 1995. In the past 10 years, MySQL has grown into a robust multithreaded, multi-user, RDBMS with an estimated six million installations. Many of the early criticism of MySQL was based on features that the MySQL team had not yet implemented into the database program. However, many of these features (Triggers, Stored Procedures, Row Locking) have been added with version 5.0.

MySQL offers a nice balance of reliability, security, and complexity while still remaining simple. Since MySQL is a company driven by a profit line, it offers many of the same features and support as the large commercial database systems. There is training and certification available. There are many levels of support an organization may purchase that go beyond the user forums and a large number of books. (A search on Amazon.com yields 210 resources for MySQL). There are typically large updates every year or two which strive to bring the database up to full compliance with ANSI/ISO SQL. There are also annual MySQL user conferences that provide hands on training with the developers as well as glimpses of what some users are doing with this database.

As MySQL was being developed they focused on what the creators call the 5-minunte installation. This means that they would like the user to be able to download their database and have a working version of it on their computer within 5 minutes. With MySQL I was able to be up and running within one day. In fact it is almost too easy to install as seen with issues concerning the default installation and the default users it creates. These problems have been correct in the most recent version.

For the BNZ - LTER the MySQL database has worked perfectly with our recent switch to Linux and Apache. We needed a relational database that could help us with more robust data mining and EML development. With so much support for the LAMP project MySQL was a logical solution. However, because the website was already coded in Coldfusion MX, we did not use PHP. Perhaps in the future we will explore a PHP migration.

MySQL develops many tools that help the DBA and database users interact with the databases. The three main tools that we use are:

  • MySQL Migration Toolkit: A wizard driven database migration kit. This tool quickly moves an Access database into a MySQL database, making transitions between databases painless.
  • MySQL Administrator: Offers an easy GUI for updating users, creating new tables, or managing security settings.
  • MySQL Query Browser: An intuitive GUI for generating and optimizing SQL queries which is integral to a relational database and dynamic web coding.

In addition to the suite of tools that MySQL develops, there are a large number of programs (commercial and open source) available. We employ the use of several of these. Navicat and Toad are two of the commercial products we use to help manage tables and insert data.

The features that are missing in MySQL currently are easy to overlook in an ecological environment. This is especially true when many of the LTER sites look at their bottom line and current level of on site database support. In the case of BNZ, where we have one data manager that fills all of the IT needs, we need an easy, fast, well documented, free database. Comparisons between databases have shown that MySQL is the fastest database currently available. There are currently many large scale businesses (Yahoo, NASA, Ticketmaster, etc) that are moving their mission critical databases from the Oracles and Microsoft's over to MySQL. These large scale operations are employing MySQL and saving in some cases millions of dollars. Having said that, MySQL is not designed for the levels of referential integrity that a Postgres will provide. It often relies on application code to perform this critical check. This would be a huge problem to me if I had many different users updating my database. However, in my system I am the only Database user that can update or do "unique" queries. Anything added to the database is QC outside of the database as well as inside the database with row locking and cascading updates and deletes. If you have many users and are very concerned with referential integrity I might recommend one of the more robust databases. However, be aware that more database knowledge and planning will be needed.

I do not want to jump into specifics of the database (there are over a 100 books that can do that), but I do want to express that while MySQL is not Oracle, it is not trying to be either. It is a very fast database that has the ability to perform on a relation database level. There is a large amount of development and resources being poured into this database that will ensure it's future. In the 2 years that I have been working with the MySQL database I have witnessed vast improvements to security, speed, abilities, and support.