Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to add an index to a table after it has been created. This is an alias of the clause of the ALTER TABLE statement that adds an index. You can add indexes only to MyISAM, InnoDB, and BDB types of tables. You can also create these tables with indexes, as shown in the CREATE TABLE statement later in this chapter.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR data types of MyISAM tables can be indexed with FULLTEXT indexes.

Creating UNIQUE indexes

CREATE UNIQUE INDEX index
ON table (column, ...)

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed, or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses:

CREATE UNIQUE INDEX client_name
ON clients (client_lastname, client_firstname(4), rec_date);

In this example, an index is created called client_name. It is based on the last names of clients, the first four letters of their first names, and the dates that the records were created. This index is based on it being unlikely that a record would be created on the same day for two people with the same last name and a first name starting with the same four letters.

To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.

Creating FULLTEXT indexes

CREATE FULLTEXT INDEX index
ON table (column, ...)

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses:

CREATE FULLTEXT INDEX client_notes
ON clients (business_description, comments);

In this example, an index is created called client_notes. It is based on two columns, both of which are TEXT columns.

To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.

Creating SPATIAL indexes

CREATE SPATIAL INDEX index
ON table (column, ...)

SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with version 4.1 of MySQL. Here is an example in which first a table and then a spatial index is created:

CREATE TABLE squares 
(square_id INT, square_name VARCHAR(100), 
square_points POLYGON NOT NULL);

CREATE SPATIAL INDEX square_index 
ON squares (square_points);

Notice that when we create the table, we specify that the column square_points is NOT NULL. This is required to be able to index the column. Let’s insert two rows of data:

INSERT INTO squares 
VALUES(1000, 'Red Square', 
(GeomFromText('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)))')) ),
(1000, 'Green Square', 
(GeomFromText('MULTIPOLYGON(((3 3, 3 5, 5 5, 4 3, 3 3)))')) );

Here we added two squares by giving the five points of the polygon: the starting point (e.g., for the first row, x=0, y=0), the left top point (x=0, y=3), the right top point (x=3, y=3), the right bottom point (x=3, y=0), and the ending point (x=0, y=0) for good measure, which is the same as the starting point. So, the first row contains a square that is 3×3 in size, and the second contains a square that is 2×2 in size. Using the AREA() function we can find the area of each:

SELECT square_name AS 'Square',
AREA(square_points) AS 'Area of Square'
FROM squares;

+--------------+----------------+
| Square       | Area of Square |
+--------------+----------------+
| Red Square   |              9 | 
| Green Square |              3 | 
+--------------+----------------+

If we want to find which square contains a given point on a Cartesian plane (e.g., x=1, y=2), we can use the MBRContains() function like so:

SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)'));

+-------------+
| square_name |
+-------------+
| Red Square  | 
+-------------+

To see how the index we added is involved, we would run an EXPLAIN statement using the same SELECT statement:

EXPLAIN SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)')) \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
         type: range
possible_keys: square_index
          key: square_index
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where

Notice that the SQL statement is using the square_index spatial index that we created.