MySQL Tutorial
Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
The users cannot see the indexes, they are just used to speed up searches/queries.
An index helps to speed up SELECT queries and WHERE clauses
--Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
--We can also create unique index:
--Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
The SQL statement below creates an index named "idx_SUBJECT" on the "SUBJECT" column in the "Students" table:
CREATE INDEX idx_SUBJECT
ON Students (SUBJECT); If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
CREATE INDEX idx_INFO
ON Students (SUBJECT,NAME);
The DROP INDEX statement is used to delete an index in a table.
DROP INDEX index_name;