Ten MySQL Best Practices
Pages: 1, 2

6. Create your own sequence generation scheme.

This "best practice" is related to the last one. MySQL provides a MySQL-specific tool for generating unique sequences using the AUTO_INCREMENT keyword. Specifically, MySQL enables you to define one column in each table as auto-generating unique values whenever you do an insert. Unfortunately, it is specific to MySQL and comes with some important limitations:

• You can have only one AUTO_INCREMENT column per table.

• You cannot have a unique sequence for multiple tables. For example, you cannot use AUTO_INCREMENT to guarantee uniqueness for columns in separate tables so that a unique value in one table does not appear in the other table as well.

• You cannot easily determine from an application what values MySQL has automatically generated.

It is better to develop your own cross-platform approach to sequence generation. In the forthcoming O'Reilly book Java Best Practices: J2EE Edition, I describe such an approach. In short, this approach involves the creation of a special table in the database that can seed different sequences. You can have as many sequences as you like and even share a sequence across multiple columns--you simply access the sequence by name. An application then uses the seed from this special table to generate unique values in memory. There is no need to go back to the database until the list of available sequences for a seed is exhausted.

7. Do not mix display code and database code.

It is very difficult to maintain an application in which database code mingles with display code. An example of such a monstrosity is a JSP page that contains JDBC code. This situation should never happen.

Instead, applications are much easier to maintain when you divide application logic according to the model-view-controller (MVC) design pattern. This best practice applies both to Web programming and GUI application programming. In short, MVC forces you to split your code between the model (a component housing your database code), a view (a component that describes the user interface), and a controller (an object that handles user actions).

8. Normalize with zeal, denormalize sparingly.

Normalization is the process of removing redundant data from your database design. By removing redundancies, you minimize the places where you have to maintain data consistency. The result is increased system maintainability. You should always take your data model to the Third Normal Form (3NF).

In some cases, normalization incurs performance penalties. These performance penalties do not occur, however, as often as people might lead you to believe. The process of optimizing the data model for performance by compromising its normalization is called denormalization. It is important to note that a data model that has not gone through normalization is not a denormalized database--it is instead unnormalized.

Denormalization is a very conscious, deliberate process by which normalizations to a data model are removed for specific performance goals. It should always occur after the data model is fully normalized and it should only occur when the benefits are readily provable.

9. Use connection pooling in Web servers and application servers.

Connecting to a database is an expensive operation. For client/server applications, this cost goes mostly without notice. Web servers and application servers, however, are constantly starting and ending user sessions with the database and therefore suffer if a new connection is made for each user session. Fortunately, most programming languages these days provide tools for pooling database connections. In other words, a connection pool enables you to reuse the same connection for multiple user sessions without constantly connecting and disconnecting. You should seek out and take advantage of this support in whatever your choice of programming language may be.

10. Tune your queries with EXPLAIN SELECT.

EXPLAIN SELECT is a critical SQL command to understand. Its output can help you understand how your SQL will perform before you ever execute a single query with them. It can also provide strong indications of where you need to make changes, such as with the creation of indexes.

O'Reilly & Associates recently released (April 2002) Managing & Using MySQL, 2nd Edition.

• Sample Chapter 13, Java, is available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

George Reese is the founder of two Minneapolis-based companies, enStratus Networks LLC (maker of high-end cloud infrastructure management tools) and Valtira LLC (maker of the Valtira Online Marketing Platform). He is also the author of technology books such as the MySQL Pocket Reference, Database Programming with JDBC and Java, and Java Database Best Practices.

---

Return to ONLamp.com.