- Indexes on data tables speed up searches which cut down the time it takes to execute complex queries. Scanning a sorted index is quicker and more efficient access than reading a whole table. MySQL supports several index types that can be created on a table.
- MySQL will automatically create an index on column(s) used as a Primary key.
- Unique Indexes require that a value or a set of values be unique across the table in the columns on which the index is defined. But unlike primary key indexes, null values are allowed. We can also create Regular (non-unique) indexes that permits duplicate values and null values on the indexed columns.
- When using a foreign key on columns in a child InnoDB table, the child’s foreign key columns and the referenced columns in the parent table must both be indexed.
- Full-text indexes support full-text searches of the values in the columns on which the index is defined. A full-text index can be used only with tables of MyISAM type and only on CHAR, VARCHAR, and TEXT columns.
- To add indexes on a table, we can use indexing options in column definition, either at Creation time or via an ALTER TABLE statement; or create indexes later by using the CREATE INDEX statement explicitly.
CREATE INDEX Statement
- A CREATE INDEX statement can be used to explicitly create indexes on various tables.
- Using CREATE INDEX, you can add unique, regular, and full-text indexes to a table, but not primary key or foreign key indexes.
Syntax:
CREATE INDEX <index_name> ON <table_name>(<column_name>);
Example:
Creating Index
mysql> CREATE INDEX pname_ind1 on passenger(passenger_name);
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
Removing Index
mysql> DROP INDEX pname_ind1 on passenger;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Index Example in MySQL Server
Creating Index
mysql> CREATE INDEX pname_ind1 on passenger(passenger_name);
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
Removing Index
mysql> DROP INDEX pname_ind1 on passenger;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Recent Comments