3Daet.com | Indexes

How to create indexes using the ALTER statement.

Create searchable databases.

Date Created:Friday December 29th, 2006 03:41 AM
Date Modified:Friday August 01st, 2008 01:12 AM
Four types:

INDEX - standard
UNIQUE - each row has unique value for column with unique index
FULLTEXT - for FULLTEXT searches
PRIMARY KEY - particular to unique


ALTER TABLE revisions ADD UNIQUE(file_name)


# without specifying an index name, it uses the column name:
ALTER TABLE revisions ADD INDEX(file_name)

# specifying an index name:
ALTER TABLE revisions ADD INDEX file_index(file_name)

mysql> show indexes from comment;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| comment | 0          | PRIMARY    | 1            | comment_id  | A         | 67          | NULL     |        |      | BTREE      |         |
| comment | 1          | short_desc | 1            | short_desc  |           | NULL        | NULL     |        |      | FULLTEXT   |         |
| comment | 1          | short_desc | 2            | long_desc   |           | NULL        | NULL     |        |      | FULLTEXT   |         |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


show create table comment;

CREATE TABLE `comment` (
  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link_type` char(32) NOT NULL,
  `link_id` int(10) unsigned NOT NULL,
  `short_desc` text NOT NULL,
  `long_desc` text NOT NULL,
  PRIMARY KEY (`comment_id`),
  FULLTEXT KEY `short_desc` (`short_desc`,`long_desc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |