Why Indexing Can Make Database Faster
What's Index?
An index is a database structure to improve the performance of database activity. In the first place, the database will perform the search using sequential search/linear search (opens in a new tab). By using linear search there's no problem to search a record in 100 records. However if the data grows up to 1 million records, the performance would be slow and not feasible.
How does the index work?
- B-tree is the most common algorithm used in indexing, suited to most of all the case searching operations like equal, gte, lte, etc.
- Hash is key value indexing
- Fulltext is used for search engine
B-Tree (Balanced Tree) - example
Visualization:
https://www.cs.usfca.edu/~galles/visualization/BTree.html (opens in a new tab)
Tradeoff of Index
By looking at the visualization of B-Tree while inserting the data, there's a process to make the Tree balanced. This means, that inserting it needs more resources & time which makes the inserting/updating slower.
How to create Index
For example, we have a table like this:
CREATE TABLE users (
id SERIAL,
name VARCHAR(100),
email VARCHAR(100),
PRIMARY KEY (id),
CONSTRAINT email_unique UNIQUE (email)
)By defining CONSTRAINT we also created the index automatically.
To create another index we can run these commands:
CREATE INDEX user_name_index ON users (name);