Good Practice / Bad Practice: CREATE TABLE and the Storage Engine

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need to create the same database on your production server. If the same script creates a table differently on both servers, that might cause you a lot of headache later on. At Open Query, we strive to minimise (or preferrably eliminate) headaches.

One of the parts of the create table statement that has the largest impact is the storage engine specification. When you omit the storage engine from the create table statement, your table is automatically created with the default storage engine type configured for the server. Since the storage engine is a very important choice when designing your tables, you want to make sure that it is always the correct type.

Here’s an example: instead of writing CREATE TABLE city (city_id int, city_name varchar(100)) you should write: CREATE TABLE city (city_id int, city_name varchar(100)) ENGINE=InnoDB

It is a simple adjustment, but it will save you from possible problems if you just make a habit out of specifying the storage engine.

But wait, there one more thing! It’s also very important you have sql_mode=NO_ENGINE_SUBSTITUTION in your my.cnf, otherwise your table may still silently become a MyISAM table if your desired engine (for any reason) is disabled in the binary, configuration, or at runtime. With this setting error, such a situation will cause an error – so you know for sure.

In our good practice / bad practice series, we will provide you with byte/bite sized pieces of advice on what we consider good (or bad) practice in MySQL-land. The topics can be just about anything, so expect random things to come up. Also, the level of advancedness greatly varies. A topic might be a no-brainer for some, a reminder for others and a revelation for a third person. We strive to tender to all of you!

2 Responses to “Good Practice / Bad Practice: CREATE TABLE and the Storage Engine”

  1. If your servers are configured differently, that’s bad practice too.

    Surely your my.cnf and entire platform installation is in source control in a standard place, so that you can use it to install your development servers?

    There are a few cases where this comes unstuck, such as sizing the buffer pool or key buffer based on available memory (for example, maybe our production servers have 32G of ram, but my dev systems are just 512M VMs). In this case I have an install-time script generate the my.cnf based on how much ram is available.

  2. @Mark: in an ideal world, that is definitely the case (this will prolly come up in another episode of GP/BP :) ). In this poor real world, most small to medium-sized companies don’t have a setup that well structured.

Leave a Comment