Special

Introducing the “Welcome to Xojo” Bundle!

New to Xojo and looking for guidance? We've put together a terrific bundle to welcome you! Xojo Bundle

This bundle includes six back issues of the magazine -- all of year 21 in printed book and digital formats -- plus a one-year subscription (beginning with 22.1) so you'll be learning all about Xojo for the next year. It's the perfect way to get started programming with Xojo. And you save as much as $35 over the non-bundle price!

This offer is only available for a limited time as supplies are limited, so hurry today and order this special bundle before the offer goes away!

Article Preview


Buy Now

Issue 11.3 ('XDC 2013')
Instant purchase and download via GumRoad!

COLUMN

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 TABLE statement 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 might be 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.