🤔 What is Database Index?

💎 What is it?

Simply put, you can search data from database sooooo much faster

Normaly SQL searches all data from 0, it is like you search some word in dictionary from page 0.

So what do you need to find quickly?

▼ something like that, you can find so much faster! Yes it's like Index

💎 When you should use it?

You could search without index until around 1000 without any problem, but from around 10000 you should add index!

💎 How should I check it?

We need to find how SQL tries to search data which we want.
How? Just use Explain command

--just add "EXPLAIN" before query
EXPLAIN SELECT * FROM member WHERE Id=1;

📕 select_type

• SIMPLE : simple SELECT (not use UNION or SUB QUERY)

📕 type

• ALL : search all records (= table scan. Scan is like copy scan, from left to right, just ALL of them)
• Index : use index
• Ref : use index, but not UNIQUE or PRIMARY KEY (🤩 We want it!!)
• EQ_REF : index is UNIQUE or PRIMARY KEY

📕 rows

Number of records of query

For example there is 50,000 records now, and rows is 45780, so almost 92% records SQL searched.

💎 Let's create Index

▼ show index information (shouldn't show anything so far)

SHOW INDEX FROM hoge_table;

▼ create index

--ALTER TABLE hoge_table ADD INDEX hoge_index_name(column_name);
ALTER TABLE member ADD INDEX member_index_by_id(Id);

▼ check same SELECT SQL

EXPLAIN SELECT * FROM member WHERE Id=1;

▼ Now rows is only 1! (SQL searched only one record/column)

📕 index_type

• Btree: It is Balanced tree, one of the searching algorithms. It is similar to Binary tree

💎 Primary key, Unique key

- primary key

Actually setting primary key is the same as setting index, and faster than normal Index, so you should check it at first

- unique key (ex. email)

it is also setting unique keys means using index

▼ Priority is like this

Primary key > Unique key > index

💎 Disadvantage

1. Something which changes record list because it needs to create new index file.

2.If you set index which starts LIKE or %, SQL would
search all records