Deep Dive On MySQL Tables
Part 3: Table Options
Issue: 11.3 (May/June 2013)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a Sr. Data Modeler for a well-known national retailer. But in his 17 years of IT experience, he has been everything from a PC Technician to iSeries System Administrator to iSeries Programmer and Sr. Technical Lead. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 17,547
Starting Page Number: 77
Article Number: 11311
Related Link(s): None
Excerpt of article text...
Well we have covered two-thirds of the MySQL
CREATE TABLEstatement and in this installment we are going to cover one of the last two sections: table options. Then, in the last installment, we will cover partition options.
The table options are just that; settings for the table as a whole. Many of these options apply only to the MyISAM table engine. Even though the MySQL community strongly encourages the use of the InnoDB engine over the the MyISAM engine for a plethora of good reasons I am still going to cover the MyISAM options because it is still widely used by existing MySQL database installations. Many of these MyISAM options are designed to enhance the performance of the interaction with these kinds of tables, but they can come with a price of more complicated and involved configurations and hidden dangers of data corruption. You should be very careful in your selection of which option(s) you enable and you should test it VERY carefully!
The first table option we will cover is the engine selection. This is a critical option so we are going to spend some time talking about the various choices that are open to you. You can see which engines are available and which is the default in your current MySQL instance by using the "show engines" statement.
There are basically nine types of engines available to be used when creating a table. The first is InnoDB. This engine is the default engine that is used for new tables. It is optimized for OLTP workloads and it is ACID complient. Basically, if you are writing any kind of database application using MySQL then, InnoDB is your engine of choice.
MyISAM is the engine that was used prior to InnoDB. While the MySQL are not telling anyone to stay away from this engine, they do not recommend its usage for OLTP application development anymore because the InnoDB does a much better job on all fronts. They do kind of hint around that for some read-intenisve workloads MyISAM
mightbe appropriate, but then caveat it by saying that you can tweak InnoDB to mimic this type of situation and still get higher performance and scalablity that comes from InnoDB. My two cents on the matter is, don't use MyISAM unless you really, really have to.
...End of Excerpt. Please purchase the magazine to read the full article.