INDEX
An INDEX is a collection of keys that are created from one or more columns in the table. The keys act as a pointer to the record values in the row of the table. It is just like an Index of the book. In a book, if we want to study about certain topic, we would not go pages after pages looking for that topic. We would, instead, look for that topic in the index and the page number it is referring to.
Benefits:
Helps in locating the records related to the keys created quickly
Improves the performance of the query
Caution: Since, an INDEX consume space in the database, it is only wise to Create Index on Columns that are frequently used or searched for in the database.
Indexes are automatically created when PRIMARY KEY (PK) constraint is used. Indexes other than PK are referred to as Secondary Indexes.
Secondary Index with constraints can be created when using UNIQUE or FOREIGN KEY constraints
Secondary Index without constraints are created by just using INDEX keyword
We have already covered how to define PK, FK, and other constraints in the table. This section demonstrates on how to create a Secondary Index without constraints.
CREATE INDEX
Indexes can be created
While creating a new table, or
On existing table
In our example table "students", we can create index as follow:
CREATE INDEX WHILE CREATING TABLE
CREATE TABLE students (
student_id INT NOT NULL
first_name VARCHAR(255) NOT NULL
last_name VARCHAR(255) NOT NULL
email VARCHAR(255)
PRIMARY KEY (student_id)
INDEX (first_name)
);
CREATE INDEX ON EXISTING TABLE
CREATE INDEX first_name_idx
ON students(first_name)
CREATE INDEX index_name
ON table_name (column_names);
CREATE UNIQUE INDEX ON EXISTING TABLE
We can also create a UNIQUE Index on a table. In such an Index, duplicate values are prohibited.
CREATE UNIQUE INDEX index_name
ON table_name(column_names);
DELETE INDEX
To delete an existing index in a table, we use ALTER TABLE statement, and drop the index.
ALTER TABLE table_name
DROP INDEX index_name;
VIEW INDEX
To view an existing index in a table,
SHOW INDEX FROM table_name [FROM database_name];