Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

13.1.4. CREATE INDEX Syntax

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
    [WITH PARSER parser_name]

index_col_name:
    col_name [(length)] [ASC | DESC]

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.2, “ALTER TABLE Syntax”. For more information about how MySQL uses indexes, see Section 7.4.5, “How MySQL Uses Indexes”.

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.5, “CREATE TABLE Syntax”. CREATE INDEX enables you to add indexes to existing tables.

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.

For CHAR, VARCHAR BINARY, and VARBINARY columns, indexes can be created that use only part of a column, using col_name(length) syntax to specify an index prefix length. Index entries consist of the first length characters of each column value for CHAR and VARCHAR columns, and the first length bytes of each column value for BINARY and VARBINARY columns. BLOB and TEXT columns also can be indexed, but a prefix length must be given.

The statement shown here creates an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name(10));

If names in the column usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters for non-binary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set.

In MySQL 5.1:

  • You can add an index on a column that can have NULL values only if you are using the MyISAM, InnoDB, BDB, or MEMORY storage engine.

  • You can add an index on a BLOB or TEXT column only if you are using the MyISAM, BDB, or InnoDB storage engine.

An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Some storage engines allow you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name. The allowable type_name values supported by different storage engines are shown in the following table. Where multiple index types are listed, the first one is the default when no index_type specifier is given.

Storage Engine Allowable Index Types
MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE

Examples:

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

TYPE type_name can be used as a synonym for USING type_name to specify an index type. However, USING is the preferred form. In addition, the index name that precedes the index type in the index specification syntax is not optional with TYPE: Unlike USING, TYPE is not a reserved word and thus is interpreted as an index name.

If you specify an index type that is not legal for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type.

FULLTEXT indexes are supported only for MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. See Section 12.7, “Full-Text Search Functions”. A WITH PARSER clause can be specified to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is legal only for FULLTEXT indexes. See Section 27.2, “The MySQL Plugin Interface”, for details on creating plugins.

SPATIAL indexes are supported only for MyISAM tables and can include only spatial columns that are defined as NOT NULL. Chapter 18, Spatial Extensions, describes the spatial data types.


 
 
  Published under the terms of the GNU General Public License Design by Interspire