Creating, Dropping, Indexing, and Altering Tables

MySQL allows you to create tables, drop (remove) them, and change their structure using the CREATE TABLE, DROP TABLE, and ALTER TABLE statements. For each of these statements, there are MySQL-specific extensions that make them more useful. The CREATE INDEX and DROP INDEX statements allow you to add or remove indexes on existing tables.

The CREATE TABLE Statement

Tables are created with CREATE TABLE. The full syntax for this statement is pretty horrendous because there are so many optional clauses, but in practice this statement is usually fairly simple to use. For example, all of the CREATE TABLE statements that we used in Chapter 1 are reasonably uncomplicated.

Ironically, much of this additional complexity comes from clauses that MySQL parses and then throws away! You can see this complexity by referring to Appendix D. Take a look at the entry for CREATE TABLE and notice how much of the statement syntax is devoted to the REFERENCES, CONSTRAINT, and CHECK. clauses. Those clauses concern foreign keys, referential integrity, and input value restriction. MySQL doesn't support these features, but it parses the syntax to make it easier to use table definitions that you've created in other database systems. (You can use that code more easily with less editing.) If you're writing your own table descriptions from scratch, you can completely forget about having to deal with those clauses. I won't say another word about them in this section.

The CREATE TABLE specifies, at a minimum, the table name and a list of the columns in it. For example:

CREATE TABLE my_table
(
   name CHAR(20),
    age INT NOT NULL,
    weight INT,
    sex ENUM('F','M')
)

In addition to the columns that make up a table, you can specify how the table should be indexed when you create it. Another option is to leave the table unindexed when you create it and add the indexes later. That's a good strategy if you plan to populate the table with a lot of data before you begin using it for queries. Updating indexes as you insert each row is much slower than loading the data into an unindexed table and creating the indexes afterward.

We have already covered the basic syntax for the CREATE TABLE. statement in Chapter 1 and discussed how to describe the column types in Chapter 2, "Working with Data in MySQL." I assume you've read those chapters, so we won't repeat the material here. Instead, for the remainder of this section, we'll deal with some important extensions to the CREATE TABLE statement that were introduced in MySQL 3.23 and that give you a lot of flexibility in how you construct tables:

• Table storage-type specifiers

• Creation of a table only if it doesn't already exist

• Temporary tables that are dropped automatically when the client session ends

• The capability to create a table simply by selecting the data you want it to contain

Table Storage-Type Specifiers

Prior to MySQL 3.23, all user-created tables used the ISAM storage method. In MySQL 3.23, you can explicitly create tables in any of three types by specifying TYPE= type after the column list part of the CREATE TABLE statement.type can be MYISAM, ISAM, or HEAP. For example:

CREATE TABLE my_tbl (i INT, c CHAR(20)) TYPE = HEAP

You can convert tables from one type to another with ALTER TABLE:

ALTER TABLE my_tbl TYPE = ISAM
ALTER TABLE my_tbl TYPE = MYISAM
ALTER TABLE my_tbl TYPE = HEAP

It may not be a good idea to convert a table to type HEAP, however, if you expect the table to last beyond server shutdown. HEAP tables are held in memory and disappear when the server exits.

The general characteristics of these three table types are as follows:

• MyISAM Tables. The MyISAM storage format is the default table type in MySQL as of version 3.23:

  • Files can be larger than for the ISAM storage method if your operating system itself allows larger files.

  • Data are stored in machine-independent format with the low byte first. This means you can copy tables from one machine to another, even if they have different architectures.

  • Numeric index values take less storage space because they are stored with the high byte first. Index values tend to vary faster in the low-order bytes, so high-order bytes are more subject to compression.

  • AUTO_INCREMENT handling is better than for ISAM tables. The details of this are discussed in Chapter 2 in the section "Working with Sequences."

  • Several indexing constraints have been relaxed. For example, you can index columns that contain NULL values, and you can index BLOB and TEXT types.

  • For improved table integrity checking, each table has a flag that is set when the table is checked by myisamchk. You can use myisamchk —fast to skip checks on tables that haven't been modified since the previous check, which makes this administrative task quicker. Tables also have a flag indicating whether a table was closed properly. If the server shuts down abnormally or the machine crashes, the flag can be used to detect tables that need to be checked at server startup time.

• ISAM Tables. The ISAM storage format is the older format used prior to MySQL 3.23 but still available currently. In general, MyISAM tables are preferable to ISAM tables because they have fewer limitations. Support for ISAM tables will likely fade as this storage format is supplanted by the MyISAM table format.

• HEAP Tables. The HEAP storage format creates in-memory tables that use fixed-length rows, which makes them very fast. It also means they are temporary in the sense that they disappear when the server terminates. However, in contrast to temporary tables created with CREATE TEMPORARY TABLE, HEAP tables are visible to other clients. Several constraints apply to HEAP tables that do not apply to MyISAM or ISAM tables:

  • Indexes are used only for '=' and '<=>' comparisons.

  • You cannot have NULL values in indexed columns.

  • BLOB and TEXT columns cannot be used.

  • AUTO_INCREMENT columns cannot be used.

Provisional Table Creation

To create a table only if it doesn't already exist, use CREATE TABLE IF NOT EXISTS. You can use this for an application that makes no assumptions about whether tables that it needs have been set up in advance and simply attempts to create the tables as a matter of course. The IF NOT EXISTS modifier is particularly useful for scripts that you run as batch jobs with mysql. In this context, a regular CREATE TABLE statement doesn't work very well. The first time the job runs, the tables are created, but the second time an error occurs because the tables already exist. If you use IF NOT EXISTS, there is no problem. The first time the job runs, the tables are created, as before. For second and subsequent times, table creation attempts fail, but no error is generated. This allows the job to continue processing as if the attempt had succeeded.

Temporary Tables

You can use CREATE TEMPORARY TABLE to create temporary tables that disappear automatically when your session ends. This is handy because you don't have to bother issuing a DROP TABLE statement explicitly to get rid of the table, and the table doesn't hang around if your session terminates abnormally. For example, if you have a canned query in a file that you run with mysql and you decide not to wait for it to finish, you can kill the script in the middle with impunity and the server will remove any temporary tables.

In older versions of MySQL, there are no true temporary tables, except in the sense that you consider them temporary in your own mind. For applications that need such a table, you must remember to drop it yourself. If you forget to drop it, or an error occurs in the client causing it to exit early, the temporary table hangs around until someone notices and removes it.

A temporary table is visible only to the client that creates the table. The name can be the same as that of an existing permanent table. This is not an error, nor does the existing permanent table get clobbered. Suppose you create a temporary table in the samp_db database named member. The original member table becomes hidden (inaccessible), and references to member refer to the temporary table. If you issue a DROP TABLE member statement, the temporary table is removed and the original member table "reappears." If you simply disconnect from the server without dropping the temporary table, the server automatically drops it for you. The next time you connect, the original member table is visible again.

The name-hiding mechanism works only to one level. That is, you cannot create two temporary tables with the same name.

Creating Tables from SELECT Results

One of the key concepts of relational databases is that everything is represented as a table of rows and columns, and the result of every SELECT is also a table of rows and columns. In many cases, the "table" that results from a SELECT is just an image of rows and columns that scroll off the top of your display as you continue working. Prior to MySQL 3.23, if you wanted to save the results of a SELECT in a table for use in further queries, you had to make special arrangements:

In MySQL 3.23, that has all changed. The CREATE TABLE … SELECT statement eliminates all that ugly messing around and makes it possible to cause a new table to spring into existence on the fly using the result of an arbitrary SELECT query. You can do this in a single step without having to know or specify the data types of the columns you're retrieving. This makes it exceptionally easy to create a table fully populated with the data you're interested in, ready to be used in further queries.

You can copy a table by selecting its entire contents (no WHERE clause), or create an empty copy by adding a WHERE clause that always fails:

CREATE TABLE new_tbl_name SELECT * FROM tbl_name
CREATE TABLE new_tbl_name SELECT * FROM tbl_name WHERE 1 = 0

Creating an empty copy is useful if you want to load a data file into the original file using LOAD DATA, but you're not sure if you have the options for specifying the data format quite right. You don't want to end up with malformed records in the original table if you don't get the options right the first time! Using an empty copy of the original table allows you to experiment with the LOAD DATA options for specifying column and line delimiters until you're satisfied your input data are being interpreted properly. After you're satisfied, you can load the data into the original table.

You can combine CREATE TEMPORARY TABLE with SELECT to create a temporary table as a copy of itself:

CREATE TEMPORARY TABLE my_tbl SELECT * FROM my_tbl

That allows you to modify the contents of my_tbl without affecting the original. This is useful when you want to try out some queries that modify the contents of the table without changing the original table. To use pre-written scripts that use the original table name, you don't need to edit them to refer to a different table; just add the CREATE TEMPORARY TABLE statement to the beginning of the script. The script will create a temporary copy and operate on the copy, and the server will delete the copy when the script finishes.

To create a table as an empty copy of itself, use a WHERE 0 clause in conjunction with CREATE TEMPORARY … SELECT:

CREATE TEMPORARY TABLE my_tbl SELECT FROM my_tbl WHERE 1 = 0

However, there are several caveats to consider when creating tables on the fly. When you create a table by selecting data into it, the column names are taken from the columns that you are selecting. If a column is calculated as the result of an expression, the "name" of the column is the text of the expression. An expression isn't a legal column name. You can see this by running the following query in mysql:

mysql> CREATE TABLE my_tbl SELECT 1;
ERROR 1166 at line 1: Incorrect column name '1'

To make this work, provide a column alias to give the column a legal name:

mysql> CREATE TABLE my_tbl SELECT 1 AS my_col;
Query OK, 1 row affected (0.01 sec)

A related snag occurs if you select columns from different tables that have the same name. Suppose tables t1 and t2 both have a column c and you want to create a table from all combinations of rows in both tables. You can provide aliases to specify unique column names in the new table:

CREATE TABLE t3 SELECT t1.c AS c1, t2.c AS c2 FROM t1, t2;

Creating a table by selecting data into it does not automatically copy any indexes from the original table.

The DROP TABLE Statement

Dropping a table is much easier than creating it because you don't have to specify anything about its contents; you just have to name it:

DROP TABLE tbl_name
						

MySQL extends the DROP TABLE statement in some useful ways. First, you can drop several tables by specifying them all on the same statement:

DROP TABLE tbl_name1, tbl_name2, …

Second, if you're not sure whether or not a table exists, but you want to drop it if it does, you can add IF EXISTS to the statement. This causes MySQL not to complain or issue an error if the table or tables named in the statement don't exist:

DROP TABLE IF EXISTS tbl_name
						

IF EXISTS is useful in scripts that you use with the mysql client because mysql, by default, exits when an error occurs, and is an error to try to remove a table that doesn't exist. For example, you might have a setup script that creates tables that you use as the basis for further processing in other scripts. In this situation, you want to make sure the setup script has a clean slate when it begins. If you use a regular DROP TABLE at the beginning of the script, it would fail the first time because the tables have never been created. If you use IF EXISTS, there is no problem. If the tables are there, they are dropped; if not, the script continues anyway.

Creating and Dropping Indexes

Indexes are the primary means of speeding up access to the contents of your tables, particularly for queries that involve joins on multiple tables. This is an important enough topic that Chapter 4, "Query Optimization," discusses why you use indexes, how they work, and how best to take advantage of them to optimize your queries. In thissection, we'll cover the characteristics of indexes and the syntax you use for creating and dropping them.

Characteristics of Indexes

MySQL provides quite a bit of flexibility in the way you can construct indexes. You can index single columns or combinations of columns. You can also have more than one index on a table if you want to be able to look up a value quickly from different columns of a table. If a column is a string type other than ENUM or SET, you may elect to index only the leftmost n characters of the column. If the column is mostly unique within the first n characters, you usually won't sacrifice performance, and may well improve it: Indexing a column prefix rather than the entire column can make an index much smaller and faster to access.

There are also some constraints on index creation, though these have tended to lessen as MySQL development continues. The following table shows some of the differences between ISAM and MyISAM tables in terms of indexing capabilities:

You can see from the table that for ISAM tables, an indexed column must be declared NOT NULL, and you cannot index BLOB or TEXT columns. The MyISAM table type removes these constraints on index creation, and relaxes some of the others. One implication of these differences in index characteristics for the two-table types is that, depending on your version of MySQL, you may simply not be able to index certain columns. For example, you can use only ISAM tables if your MySQL is older than 3.23, which means you can't index a column if you want it to be able to contain NULL values.

If you have MySQL 3.23 or later, you may have older tables that were originally created as ISAM tables. You can easily convert them to MyISAM storage format using ALTER TABLE, which allows you to take advantage of some of the newer indexing features:

ALTER TABLE tbl_name TYPE = MYISAM

Creating Indexes

You can create indexes for a new table when you use CREATE TABLE, or you can add indexes to existing tables with CREATE INDEX or ALTER TABLE.CREATE INDEX was introduced in MySQL 3.22, but you can use ALTER TABLE if your version of MySQL is older than that. (MySQL currently maps CREATE INDEX onto ALTER TABLE internally.)

You can specify that the index may or may not contain duplicate values. If it cannot, the index should be created as a PRIMARY KEY or a UNIQUE index. For a single-column unique index, this ensures that the column contains no duplicate values. For a multiple-column unique index, it ensures that no combination of values is duplicated.

A PRIMARY KEY and a UNIQUE index are very similar. In fact, a PRIMARY KEY is just a UNIQUE index with the name PRIMARY. This means that a table may contain only one PRIMARY KEY because you can't have two indexes with the same name. You can place multiple UNIQUE indexes on a table, although it's somewhat unusual to do so.

To add an index to an existing table, you can use ALTER TABLE or CREATE INDEX.ALTER TABLE is the more versatile of the two because you can use it to create a regular index, a UNIQUE index, or a PRIMARY KEY:

ALTER TABLE tbl_name ADD INDEX index_name (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

tbl_name is the name of the table to add the index to, and column_list indicates which column or columns should be indexed. If the index consists of more than one column, separate the names by commas. The index name index_name is optional, so you can leave it out and MySQL will pick a name based on the name of the first indexed column. ALTER TABLE allows you to specify multiple table alterations in a single statement, so you can create several indexes at the same time.

CREATE INDEX can add a regular or UNIQUE index to a table:

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)
CREATE INDEX index_name ON tbl_name (column_list)

tbl_name, index_name, and column_list have the same meaning as for ALTER TABLE. The index name is not optional, though. You cannot create a PRIMARY KEY with CREATE INDEX.

To create an index for a new table when you issue a CREATE TABLE statement, you use syntax similar to that for ALTER TABLE, but you specify the index-creation clauses in the part of the statement where you declare the table columns:

CREATE TABLE tbl_name
(
    …
    INDEX index_name (column_list),
    UNIQUE index_name (column_list),
    PRIMARY KEY (column_list),
    …
)

As for ALTER TABLE, the index name is optional for INDEX and UNIQUE and MySQL will pick a name if you leave it out.

As a special case, you can create a single-column PRIMARY KEY by adding PRIMARY KEY to the end of the column declaration:

CREATE TABLE my_tbl
(
    i INT NOT NULL PRIMARY KEY
)

That statement is equivalent to the following one:

CREATE TABLE my_tbl
(
    i INT NOT NULL,
    PRIMARY KEY (i)
)

Each of the preceding table-creation examples have specified NOT NULL for the indexed columns. If you have ISAM tables, that's a requirement because you cannot index columns that may contain NULL values. If you have MyISAM tables, indexed columns may be NULL, as long as the index is not a PRIMARY KEY.

If you index a prefix of a string column (the leftmost n characters of column values), the syntax for naming the column in a column_list specifier is col_name(n) rather than simply col_name. For example, the first of the following statements creates a table with two CHAR columns and an index that uses both columns. The second statement is similar, but it indexes a prefix of each column:

CREATE TABLE my_tbl
(
    name CHAR(30),
    address CHAR(60),
    INDEX (name,address)
)

CREATE TABLE my_tbl
(
    name CHAR(30),
    address CHAR(60),
    INDEX (name(10),address(20))
)

In some circumstances, you may find it necessary to index a column prefix. For example, the length of index rows has an upper bound, so you may need to use prefixes if the length of the indexed columns exceeds that bound. Prefixes are also necessary for BLOB or TEXT columns in MyISAM table indexes.

Indexing a prefix of a column constrains changes you can make to the column later; you cannot shorten the column to a length less than the prefix length without dropping the index and re-creating it using a shorter prefix.

Dropping Indexes

You can drop indexes using either the DROP INDEX or ALTER TABLE statements. Like the CREATE INDEX statement, DROP INDEX currently is handled internally as an ALTER TABLE statement and was introduced in MySQL 3.22. The syntax for index-dropping statements looks like this:

DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY

The first two of these statements are equivalent. The third is used only for dropping a PRIMARY KEY; in this case, no index name is needed because a table may have only one such key. If no index was created explicitly as a PRIMARY KEY but the table has one or more UNIQUE indexes, MySQL drops the first of them.

Indexes may be affected if you drop columns from a table. If you drop a column that is a part of an index, the column is removed from the index as well. If all columns that make up an index are dropped, the entire index is dropped.

The ALTER TABLE Statement

ALTER TABLE is a versatile statement in MySQL, and you can use it to do many things. We've already seen several of its capabilities (for creating and dropping indexes and for converting tables from one storage format to another). In this section, we'll cover some of its other talents. The full syntax for ALTER TABLE is described in Appendix D

ALTER TABLE is useful when you find that the structure of a table no longer reflects what you want to do with it. You may want to use the table to record additional information, or perhaps it contains information that has become superfluous. Maybe existing columns are too small, or perhaps you've declared them larger than it turns out you need and you'd like to make them smaller to save space and improve query performance. Or maybe you just typed in the table's name incorrectly when you issued the CREATE TABLE statement. Here are some examples:

• You're running a Web-based questionnaire, and you store the results from each submitted questionnaire as a record in a table. Then you decide to modify the questionnaire to add some additional questions. You must add some columns to the table to accommodate the new questions.

• You're running a research project. You assign case numbers to research records using an AUTO_INCREMENT column. You didn't expect your funding to last long enough to generate more than about 50,000 records, so you made the column type UNSIGNED SMALLINT, which holds a maximum of 65,535 unique values. However, the funding for the project was renewed, and it looks like you may generate another 50,000 records. You need to make the type bigger to accommodate more case numbers.

• Size changes can go the other way, too. Maybe you created a CHAR(255) column but now recognize that no value in the table is m ore than 100 characters long. You can shorten the column to save space.

The syntax for ALTER TABLE looks like this:

ALTER TABLE tbl_name
							action, …

Each action specifies a modification you want to make to the table. MySQL extends the ALTER TABLE statement by allowing you to specify multiple actions, separated by commas. This is useful for cutting down on typing, but a more important reason for this extension is that it's impossible to change tables from variable-length rows to fixed-length rows unless you can change all the VARCHAR columns to CHAR at the same time.

The following examples show some of the capabilities of ALTER TABLE.

• Renaming a table. This is easy; just specify the old name and the new name:

  ALTER TABLE tbl_name RENAME AS new_tbl_name
  								

  In MySQL 3.23, which has temporary tables, renaming a temporary table to a name that already exists in the database hides the original table for as long as the temporary table exists. This is analogous to the way that a table is hidden by creating a temporary table with the same name.

• Changing a column type. To change a column type, you can use either a CHANGE or MODIFY clause. Suppose the column in a table my_tbl is SMALLINT UNSIGNED and you want to change it to MEDIUMINT UNSIGNED. Do so using either of the following commands:

  ALTER TABLE my_tbl MODIFY i MEDIUMINT UNSIGNED
  ALTER TABLE my_tbl CHANGE i i MEDIUMINT UNSIGNED
  

  Why is the column named twice in the command that uses CHANGE? Because one thing that CHANGE can do that MODIFY cannot is to rename the column in addition to changing the type. If you had wanted to rename i to j at the same time you changed the type, you'd do so like this:

  ALTER TABLE my_tbl CHANGE i j MEDIUMINT UNSIGNED
  

  The important thing is that you name the column you want to change and then specify a complete column declaration, which includes the column name. You still need to include the name in the declaration, even if it's the same as the old name.

  An important reason for changing column types is to improve query efficiency for joins that compare columns from two tables. A comparison is quicker when the columns are both the same type. Suppose you're running a query like this:

  SELECT … FROM t1, t2 WHERE t1.name = t2.name
  

  If t1 name is CHAR(10) and t2.name is CHAR(15), the query won't run as quickly as if they were both CHAR(15). You can make them the same by changing t1.name using either of these commands:

  ALTER TABLE t1 MODIFY name CHAR(15)
  ALTER TABLE t1 CHANGE name name CHAR(15)
  

  For versions of MySQL prior to 3.23, it's essential that joined columns be of the same time, or indexes cannot be used for the comparison. For version 3.23 or above, indexes can be used for dissimilar types, but the query will still be faster if the types are identical.

• Converting a table from variable-length rows to fixed-length rows. Suppose you have a table chartbl with VARCHAR columns that you want to convert into CHAR columns to see what kind of performance improvements you get. (Tables with fixed-length rows generally can be processed more quickly than tables with variable-length rows.) The table was created like this:

  CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80))
  

  The problem here is that you need to change the columns all at once in the same ALTER TABLE statement. You can't do them one at a time or the attempt will be ineffective. If you run DESCRIBE chartbl, you'll find that the columns are still defined as VARCHAR! The reason is that if you change a single column at a time, MySQL notices that the table still contains variable-length columns and reconverts the changed column back to VARCHAR to save space. To deal with this, change all the VARCHAR columns at the same time:

  ALTER TABLE chartbl MODIFY name CHAR(40), MODIFY address CHAR(80)
  

  Now DESCRIBE will show that the table contains CHAR columns. It's exactly this type of operation that makes it important that ALTER TABLE support multiple actions in the same statement.

  There is a caveat to be aware of when you want to convert a table like this: The presence of BLOB or TEXT columns in the table will defeat any attempt to convert a table to fixed-length row format. The presence of even one variable-length column in a table causes it to have variable-length rows, and these column types have no fixed-length equivalent.

• Converting a table from fixed-length rows to variable-length rows. Okay, so chartbl is faster with fixed-length rows, but it takes more space than you'd like, so you decide to convert it back to its original form to save space. Converting a table in this direction is much easier. You only need to make one CHAR column to VARCHAR and MySQL will convert the other CHAR columns automatically. To convert the chartbl table, either of the following statements will do:

  ALTER TABLE chartbl MODIFY name VARCHAR(40)
  ALTER TABLE chartbl MODIFY address VARCHAR(80)
  

• Converting a table type. If you have upgraded from a pre-3.23 version of MySQL to 3.23 or later, you may have older tables that were created as ISAM tables. If you want to create them to MyISAM format, do so as follows:

  ALTER TABLE tbl_name TYPE = MYISAM
  

  Why would you do this? One reason, as already discussed in "Creating and Dropping Indexes," is that the MyISAM storage format has some indexing features that the ISAM format does not, such as the ability to index NULL values and BLOB and TEXT column types. Another reason is that MyISAM tables are machine independent, so you can move them to other machines by copying table files directly, even if the machines have different hardware architectures. This is discussed further in the section "Backing Up and Copying Databases" in Chapter 11.