Working with Tables
 
Creating & Dropping Index
 
MySQL can create several types of index:
 
. A regular (non-unique) index. This gives you indexing benefits but allows duplicates.
 
. A unique index. This disallows duplicate values. For a single-column index, this ensures that the column contains no duplicate values. For a multiple-column (composite) index, it ensures that no combination of values in the columns is duplicated among the rows of the table.
 
. A FULLTEXT index, used when you want to perform full text searches. This index type is supported only for MyISAM tables.
 
You can create indexes for a new table when you use CREATE TABLE, or 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 maps CREATE INDEX statements onto ALTER TABLE operations internally.)
 
ALTER TABLE is the more versatile than CREATE INDEX. You can use it to create a regular index, a UNIQUE index, a PRIMARY KEY, or a FULLTEXT index :
 
ALTER TABLE tbl_name ADD INDEX index_name (index_columns);
ALTER TABLE tbl_name ADD UNIQUE index_name (index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);
ALTER TABLE tbl_name ADD FULLTEXT (index_columns);
 
tbl_name is the name of the table to add the index to, and index_columns 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. (This is faster than adding them one at a time with individual statements.)
 
To require that an index contain only unique values, create the index as a PRIMARY KEY or a UNIQUE index. The two types of index are very similar. In fact, a PRIMARY KEY is just a UNIQUE index that has the name PRIMARY. Two differences between the types of index are:
 
. A table can contain only one PRIMARY KEY because you can't have two indexes with the name PRIMARY. You can place multiple UNIQUE indexes on a table, although it's somewhat unusual to do so.
 
. A PRIMARY KEY cannot contain NULL values, whereas a UNIQUE index can. If a UNIQUE can contain NULL values, it usually can contain multiple NULL values. The reason for this is that it is not possible to know whether one NULL represents the same value as another, so they cannot be considered equal. (BDB tables are an exception-a BDB table allows only one NULL value within a UNIQUE index.)
 
CREATE INDEX can add a regular, UNIQUE, or FULLTEXT index to a table, but not a PRIMARY KEY:
 
CREATE INDEX index_name ON tbl_name (index_columns);
CREATE UNIQUE INDEX index_name ON tbl_name (index_columns);
CREATE FULLTEXT INDEX index_name ON tbl_name (index_columns);
 
tbl_name, index_name, and index_columns have the same meaning as for ALTER TABLE. Unlike ALTER TABLE, the index name is not optional with CREATE INDEX, and you cannot create multiple indexes with a single statement.
 
To create indexes for a new table when you issue a CREATE TABLE statement, the syntax is similar to that used for ALTER TABLE, but you specify the index-creation clauses as part of the column specification list:
 
CREATE TABLE tbl_name
(
... column declarations ... INDEX index_name (index_columns),
UNIQUE index_name (index_columns),
PRIMARY KEY (index_columns),
FULLTEXT index_name (index_columns),
...
);
 
As with ALTER TABLE, the index name is optional in CREATE TABLE statements for each INDEX, UNIQUE, and FULLTEXT clause; MySQL will pick an index 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 a column declaration. As of MySQL 3.23, you can do the same for a UNIQUE index. For example, this statement:
 

CREATE TABLE my_test_tbl8
(
num_col INT NOT NULL PRIMARY KEY,
char_col CHAR(10) NOT NULL UNIQUE
);
is equivalent to the following one:
CREATE TABLE my_test_tbl9
(
num_col INT NOT NULL,
char_col CHAR(10) NOT NULL,
PRIMARY KEY (num_col),
UNIQUE (char_col)
);
 
Each of the preceding table-creation examples have specified NOT NULL for the indexed columns. For ISAM tables (and for HEAP tables prior to MySQL 4.0.2), that's a requirement because you cannot index columns that may contain NULL values.
 
For other table types, indexed columns can be NULL as long as the index is not a PRIMARY KEY.
 
To index a prefix of a string column (the leftmost n bytes of column values), the syntax for naming the column in the index definition is col_name(n) rather than simply col_name. For example, the following statement creates a table with two CHAR columns but uses only the first 10 bytes from each in the index created from those columns.
 
CREATE TABLE my_test_tbl10
(
name CHAR(30) NOT NULL,
address CHAR(60) NOT NULL,
INDEX (name(10),address(10))
);
 
Index prefixes are supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables.
 
Prefix lengths, just like column lengths, refer to bytes rather than characters. The two are the same for single-byte character sets, but not for multi-byte character sets. MySQL will store into an index value as many complete characters as will fit.
 
For example, if an index prefix is 5 bytes long and a column value consists of 2-byte characters, the index value will contain 2 characters, not 2.5 characters. In some circumstances, you may find it not only desirable but necessary to index a column prefix rather than the entire column:
 
. Prefixes are necessary for BLOB or TEXT columns in any table type that allows those column types to be indexed. The prefix may be up to 255 bytes long.
 
. The length of index rows is equal to the sum of the length of the index parts of the columns that make up the index. If this length exceeds the allowable length of index rows, you can make the index "narrower" by indexing a column prefix. Suppose a MyISAM table contains two CHAR(255) columns named c1 and c2, and you want to create an index based on both of them.
 
The length of an index row in this case would be 255+255, which exceeds the MyISAM limit of 500 bytes per index row. However, you can create the index by indexing a shorter part of one or both columns.
 
Indexing a prefix of a column constrains that changes that 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 length for the indexed part of a column.
 
If you index the first 30 bytes of a 40-byte CHAR column but then discover that you never store more than 20 bytes in the column, you might decide to save space in the table by changing the column to be only 20 bytes wide. In this case, you must drop the index first before making the column narrower. Then you can add the index again, indexing 20 or fewer bytes.
 
Columns in FULLTEXT indexes do not have prefixes. If you specify a prefix length for a FULLTEXT index, it will be ignored.
 
Dropping Indexes
 
To drop an index, use either a DROP INDEX or an ALTER TABLE statement. Like the CREATE INDEX statement, DROP INDEX was introduced in MySQL 3.22, is handled internally as an ALTER TABLE statement and cannot be used to affect a PRIMARY KEY. 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 statements are equivalent. The third is used only for dropping a PRIMARY INDEX; it is unambiguous because a table can 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 can 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.