An Interview with Randy Jay Yarger

Waliszewski:

You talk about MySQL and mSQL as "...two of the most popular applications offering public source code." Why is that?

Yarger:

I think mSQL became as popular as it did simply because it existed and because it was at least minimally usable. In the early 1990s there was an overwhelming lack of inexpensive relational database management systems (RDBMS) around and hardly any at all that used standard SQL as a query language. When mSQL was introduced, it was like water to a person who's been lost in the desert for three weeks. The water might be brackish and dirty, but they just don't care.

I don't want to sound too negative about mSQL, though, because it did solve problems for lots of people, myself included, and its user base is quite large even today. However, when MySQL came around, it was a different story. With mSQL, you have about 30-40% of the functionality of, say, an Oracle data base, and that 30-40% performs in a manner that is usable, if not quite up to the standards of an Oracle db. MySQL provides a good 80-85% of Oracle's functionality. It also does it better than Oracle, SQL Server, and Informix.

That, I think, along with the inevitable attraction of the low price tag, is what makes MySQL so popular.

Waliszewski:

How accessible is MySQL to people migrating from a Windows environment? And why would a person who is used to working in Windows want to care?

Yarger:

For the average home user whose database experience does not go past Access, learning how to develop applications using MySQL may not make much sense. However, if such a person is shopping around for someone to create their small business Web site and they are given a choice between an Access/Cold Fusion solution and a MySQL/PHP solution, they should know that the MySQL solution, if done right, will be faster, more secure, and more stable.

Now, for a Windows developer who is interested in broadening his or her horizons, I think MySQL is an easy step, and one they won't regret. The first thing to keep in mind is that MySQL is not a Unix-only product. A recent version of MySQL for Windows is available under the same terms as the Unix version. In addition, the most up-to-date version for Windows (with all of the features of the most up-to-date Unix version) is available for those who want a support contract. Purchase of the support contract helps to subsidize development of the Windows version.

I would also recommend looking at the contributed software that is not part of the main MySQL package (it can be found on the MySQL Web page). These applications include all of the GUI administration and maintenance tools that make upkeep on MySQL just as easy as on any of the high-end commercial RDBMS. Once Windows users try MySQL, I think any they will quickly notice a performance increase. Then, if they were to move to Linux, or one of the BSDs, they'd really learn how low the price/performance ratio can go!

Waliszewski:

I think we are all aware that one of the main purposes of database design is to eliminate redundancy from the database. Can you explain the technique of "normalization" in db design?

Yarger:

Attempting to eliminate redundancy in a database has always been a major design goal. Relational databases are designed as a collection of two-dimensional tables. There are a number of columns that define the content of the table, and there is one row for each actual record of data. Let's say that your table consisted of the columns 'name', 'age', and 'state'. If you were to gather information about a person's name, their current age, and the state they live in, this would be fine. However, if you wanted to know every state a person has ever lived in, you would be stuck because you can only put one state in each row. You could add more columns--'state2', 'state3', etc.,--but that is both unpleasant to look at and difficult to deal with when programming the application.

You could also put all of the states in the same column, perhaps separated by commas. In this case, you would have to add extra processing code to the application. In addition, you would lose some of SQL's ability to sort and organize data. Processing would have to be done in the application, meaning more programming time for you. The recommended solution to this problem is to 'normalize' this database by creating an entirely separate table. This table would have two columns: 'name' and 'state'. You could then look at all occurrences of a person's name in that table, which would also give you each state they have lived in; you could then join this information with the person's age, found in the original table, to end up with all of the information about that person.

One of the great strengths of SQL is that it makes the process of joining the data very simple. The word 'normalization' comes from the mathematical meaning of the word 'normal' which is similar to 'perpendicular'. Just as two people walking perpendicular to each other are going in two completely different directions that have no common component or overlap, the columns of a table should not have any redundancy or overlap.

Waliszewski:

You state that MySQL is "...nothing less than a serious competitor for the major database engines in the field of small-to-medium scale database development." What makes this true?

Yarger:

What makes MySQL a competitor to the Oracles and SQL Servers of the world is simply that it solves the same problems as the larger products at a lower cost. By choosing to leave out some features of SQL, MySQL has been able to obtain an amazing performance advantage over its larger competitors. At the same time, MySQL provides ways to work around those missing features such that development time is not significantly increased in exchange for the greater performance.

Waliszewski:

You said some features were left out of MySQL. Which ones are you referring to?

Yarger:

The major functionality missing from MySQL includes sub-selects, stored procedures, triggers, foreign keys, views, cursors, atomic operations, and transactions. Of these, sub-selects, cursors, and atomic operations are in development now and will be appearing soon.

The implementation of atomic operations will also involve the standard SQL control language. This will allow stored procedures (albeit without triggers). This means that within a few months, triggers, views, foreign keys, and transactions will be the only major functionality that MySQL will be missing. And those will be filled in over time.

However, I think the major advantage of an RDBMS such as Oracle is the scalability in the very high end. MySQL is an extremely scalable application that is able to support tables with millions of rows without slowing down. Oracle and its ilk, however, have clustering capabilities that allow it to handle practically infinite amounts of data. All of this comes at a price, though, but it's one area where MySQL needs to catch up.

Waliszewski:

Would you list your top ten tips and tricks for successfully using MySQL or mSQL?

Yarger:

1. Start small. If you are new to database design and/or programming, take your time. Create small, experimental databases, and play around.

2. Don't be afraid to try new things. Look at MySQL's rich function set. If you are coming from mSQL, it may take some time to realize just how much can be accomplished within MySQL that saves programming time for you.

3. Stress test your application. There are benchmarking tools out there. Use them, or write your own (very easy in languages like Perl). Before you deploy your application, know exactly what its limits are. You might expect that application to have only a light load, but you never know what will happen.

4. Learn SQL well. The more you know about SQL, the more you will be able to accomplish with MySQL. You should even learn the parts of SQL that MySQL doesn't currently support. In that way, you'll be ready when the feature becomes available in MySQL.

5. Understand normalization well. Once you really grasp the concept, it comes naturally. Having a well designed database will save you countless hours and dollars, especially if you expand the application. Adding features to a non-normalized database becomes a nightmare.

6. Don't be afraid to use the non-standard components of MySQL. MySQL sticks to standard SQL whenever possible, more so than even Oracle. This means that if a feature in MySQL is non-standard, there is a good chance that it just isn't possible to use in standard SQL. Instead of increasing your code size and running up development costs by inventing another component, just use the MySQL component.

7. Backup. Backup. Backup. Along with backing-up the binary copies of a database, occasionally backup SQL dumps of the database. This will allow you to easily move your database to a different platform if need be. Also, take advantage of the 'update-log' option to MySQL. This keeps track of every option that alters the database. The update-log can be 'replayed' into MySQL to recreate the exact state of the database.

8. Check out the ISAM utilities. Don't bother to learn every arcane option, but make sure you run the most common and useful modes of 'isamchk' regularly, especially if your database is updated frequently. This will ensure that application performance is retained over time.

9. Try using multiple processors. If you want to add that extra kick to your application, use a machine that supports more than one processor. MySQL is a fully-multithreaded application and will automatically take advantage of multiple processor machines.

10. Buy a support contract! Show your support to MySQL.org. Not only will you gain access to the latest version of MySQL for Windows, but you'll also have the opportunity to influence which features are implemented next. It's just the right thing to do.

Waliszewski:

Everyone is concerned about reliability and security. How do the MySQL security tables work together to make this a secure database engine?

Yarger:

MySQL security tables provide a multi-layer approach to access control. The first table, 'user', is used to verify access rights to the MySQL server itself. This table is consulted to see if the user has the right to connect to the server. If the user is allowed to connect, then every request he or she sends is checked. So, first the 'user' table is checked. Any permissions that are granted apply to every database on the system. Then the 'db' database is checked to see if the user has sufficient privileges for that particular database. The 'host' database, which grants database-level permission to entire hosts, is also checked. If the user has not yet been denied, the 'tables_priv' and 'columns_priv' tables are checked to see if the user has permission to access the specific tables and columns specified in the query. This approach to security allows administrators to fine-tune access for each individual user, as well as entire hosts, from the whole system, all the way down to specific columns within tables.

Waliszewski:

Your book has been a bestseller at Amazon.com for a while now. To what would you attribute the overwhelming success of this book?

Yarger:

MySQL and mSQL have been popular applications since the mid-1990s. However, until 1998, there was no book on mSQL, and MySQL & mSQL is the first to cover MySQL. I think the users of these applications have been waiting a long time for a guide to uncovering the power of these products. MySQL in particular has so many functions that are hardly ever noticed by users, and yet they can help immensely in reducing coding time. I think readers have responded to the book's approach of covering everything from installation and maintenance to programming and database design.

Waliszewski:

I have read comments suggesting that this book is not for power users. How would you respond to that statement?

Yarger:

There is some truth to that, but, in general, I think that in the areas of application programming and database design, as well as maintenance, the book has something for all levels. Because of the popularity of MySQL and mSQL with the 'masses,' so to speak, the book intentionally does not dwell on certain highly advanced areas. We did this so the beginner wasn't overwhelmed. However, there are several advanced topics, such as User Defined Functions and understanding the internals of MySQL, that are important and which we want to include in the next edition.

Waliszewski:

Why don't the MySQL developers think transactions are important? They say you can get everything you need through other mechanisms, but that's debatable. Now we hear that the developers have promised transactions in a future release due to demand for them. What do you think?

Yarger:

I think that transactions are a powerful feature, but for very specific applications. The most useful feature of transactions, I feel, is the ability to make a series of operations that are guaranteed to happen in order with no other query interfering. MySQL currently has that ability with table locking, and it will shortly be even easier with atomic operations. However, the ability to rollback a transaction is not something I have ever really missed. I also think that when people think of MySQL and transactions, they automatically lump in everything MySQL is lacking, like stored procedures. Now that is something I absolutely cannot wait for, and when MySQL does support stored procedures (very soon now), I will begin seriously converting my MS SQL Server officemates. And once they see that power and flexibility included in MySQL, they'll thank me for it!