Indexes

What are indexes?

Indexes on a table are similar to indexes found at the end of a book to help you reach the required topics/chapters quickly without flipping through the whole book.
Indexes are data structures storing the pointer to the actual physical location of records on the disk.
These index data structures themselves are stored on disk to make them persistent.
Indexes are generally created on columns which are most likely to be part of the SELECT expression, WHERE clauses and ORDER BY expression.
If such a query is run whose all columns are present in one or more indexes, then the query may not need to touch the tables at all. Rather, the query will get all the data from indexes only.
SELECT becomes faster because of indexes.SELECT expression.
INSERT/UPDATE/DELETE become slower because of indexes as indexes also need to be updated accordingly in runtime.SELECT expression.

Indexes can have of 2 types of data structures:

Btree (Ordered binary tree, defaul type) -> Good for =, !=, >=, >=, BETWEEN, LIKE, IN operators.
Hash (Not ordered, Hash values based, more like key-value pair) -> Good for = operator.

Indexes can be -> Unique / Non-Unique

Unique -> Primary key of a table or any other set of one or more columns which would identify a unique record in the table.
For example - Emp Id OR Customer Id and Bank Account No
Non-Unique -> Any set of one or more columns to speed up the query on non-unique columns.
For example - Index on First name and Last name.

Creating Indexes:

create unique index using btree dep_name_idx on deptt (depname);
create unique index dep_name_idx on deptt (depname);

--here, name of index is given by user

create index emp_names on emp (empfname, emplname);

--here, the name of the index will be generated by the system

alter table emp add index (empfname, emplname) using hash;

Dropping index:

drop index emp_names on emp;

alter table emp drop index emp_namess;

Viewing indexes on a table:

show index from emp;

Loading comments...