MySQL in a Nutshell [52]
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: clients
Non_unique: 1
Key_name: client_index
Seq_in_index: 1
Column_name: client_name
Collation: A
Cardinality: NULL
Sub_part: 10
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: clients
Non_unique: 1
Key_name: client_index
Seq_in_index: 2
Column_name: city
Collation: A
Cardinality: NULL
Sub_part: 5
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
As you can see in the results, there was already an index in the table clients (see row 1). The index we’ve added is called client_index. It’s based on two columns: the first 10 characters of the client_name column and the first 5 characters of the city column. Limiting the number of characters used in the index makes for a smaller index, which will be faster and probably just as accurate as using the complete column widths. The results of the SHOW INDEXES statement show a separate row for each column indexed, even though one of the indexes involves two rows.
The table in this example uses the MyISAM storage engine, which uses the BTREE index type by default, so it was unnecessary to specify a type. See Appendix A for more information about storage engines and available index types. Before MySQL version 5.1.10, the USING subclause could come either before or after the column list, but as of version 5.1.10, it must follow the column list.
ALTER TABLE: ADD clause for FULLTEXT indexes
ALTER [IGNORE] TABLE table
ADD FULLTEXT [INDEX|KEY] [index] (column,...) [WITH PARSER parser]
The ADD FULLTEXT clause adds an index to a TEXT column within an existing MyISAM table. A FULLTEXT index can also index CHAR and VARCHAR columns. This type of index is necessary to use the FULLTEXT functionality (the MATCH( ) AGAINST( ) function from Chapter 11). The INDEX and KEY keywords are optional as of MySQL version 5.
With this index, the whole column will be used for each column given. Although you can instruct it to use only the first few characters of a table, it will still use the full column for the index. The WITH PARSER clause may be used to give a parser plugin for a FULLTEXT index:
ALTER TABLE workreq
ADD FULLTEXT INDEX notes_index
(client_description, technician_notes);
SHOW INDEXES FROM workreq \G
*************************** 2. row ***************************
Table: workreq
Non_unique: 1
Key_name: notes_index
Seq_in_index: 1
Column_name: client_description
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
*************************** 3. row ***************************
Table: workreq
Non_unique: 1
Key_name: notes_index
Seq_in_index: 2
Column_name: technician_notes
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
I’ve eliminated the first row from these results because it relates to the primary index, not the one created here.
As of version 5.1 of MySQL, you can use the WITH PARSER clause to specify a parser plugin for a FULLTEXT index. This option requires that the plugin table be loaded in the mysql database. This table is part of the current installation of MySQL. If you’ve upgraded MySQL and the plugin table is not in your system’s mysql database, use the mysql_upgrade script to add it. Use the SHOW PLUGINS statement to see which plugins are installed.
ALTER TABLE: ADD clause for SPATIAL indexes
ALTER [IGNORE] TABLE table
ADD SPATIAL [INDEX|KEY] [index] (column,...)
This ADD clause is used to add a SPATIAL index. A SPATIAL index can index only spatial columns. A spatial index is used in a table that holds data based on the Open Geospatial Consortium (http://www.opengis.org) data for geographical and global positioning satellite (GPS) systems. For our purposes here, this clause is necessary to add an index for spatial extensions. For MyISAM tables, the RTREE