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.

Do you have a MySQL best practice you'd like to share? Tell us about it.
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 7 of 7.

• About pointers to files (item 4)
  2006-05-26 05:44:17  pvoosten [Reply | View]
  
  
  It should be noted that storing files outside the database and storing only pointers to files inside the database shoulb be done with care.
  The filesystem on which the files are to be stored should be secured appropriately.
  Also, the benefit of referential integrity is lost, and should be re-implemented in the business layer. Only if it is not essential that pointers to files point to actual data, using such pointers is trivial.
  Therefore, I think it should be well considered whether to use file pointers or blobs when designing a database.

• Alternative to point number 6
  2005-01-07 00:09:26  Tony Marston [Reply | View]
  
  
  Instead of using an AUTO_INCREMENT column the method I have used for years, and which works on all RDBMS systems, is as follows:
  
  SELECT MAX(id) FROM 'table'
  
  This gives the current highest value. Just add 1 for the next number. Provided that 'id' is an indexed column the query can be satisfied very, very quickly by retrieving the highest value from the index and without performing a full table scan.
  
  A problem I have had with obtaining numbers from a different table is that it is possible to import data into the data table (e.g. from a 'live' to a 'dev' database) which contains id values which are greater than the values on the sequence table, thus causing all future inserts to fail. The above method does not suffer from this problem.
• Alternative to point number 6
  2008-01-27 17:33:21  zhalbrecht [Reply | View]
  
  
  Oh no. no. no no no no no. Please don't do this. How are you guaranteeing that someone else won't do the same thing at the same time? Think the chances of that are so small you'll never have to worry? Start worrying. As someone who has had to fix this kind of issue let me assure you that problems will occur. You've made no mention of transactions and even at that point wrapping your own INSERTs in transactions manually, you really are reinventing the wheel.

• Point number 1 is bad - solution
  2003-01-05 16:48:16  anonymous2 [Reply | View]
  
  
  "1. Set a password for the "root" user and then rename the user.
  
  The first thing you should do with a clean MySQL install is set a password for the root user:
  
  Advertisement
  [01:19:00] george@firenze$ mysqladmin -u root password
  'somepassword'"
  
  In my windows system the correct sentenc is:
  
  c:\mysql>mysqladmin -u root password somepassword
  
  David Martinez
  davidmc@orangeplanet.com

• The artical
  2002-12-14 18:43:05  anonymous2 [Reply | View]
  
  
  This article is what all good books should supply before potential buyers part with their money.
  
  This article on the "Ten MySQL Best Practices" gives a full insight to what to expect in the book.
  
  I really don't have any time for parting with my money where I can't get any in depth facts or reviews on books I am interested in.
  
  Regards
  

• Mysql
  2002-11-06 16:31:18  anonymous2 [Reply | View]
  
  
  most apps developed for a backend stick with that backend(unless the backend was access).
  database abstraction is adds a significant speed reduction, mysql specific functions work well and very quickly. Not to mention that mysql allows you to use different engines for different table types in the same database.
  

• The first one is to use a real DBMS.
  2002-07-19 03:52:30  leandrod [Reply | View]
  
  
  All the tips are very fine, but then why not go the extra mile and upgrade to a real DBMS, such as PostgreSQL, SAPdb or InterBase/Firebird?
  
  It's the way of programmers to create something very simple and then add layer after layer of complexity, after what the end result gets worse and more complicated than the full solution they should have gone for in the first place.