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?
You could search without index until around 1000 without any problem, but from around 10000 you should add index!
We need to find how SQL tries to search data which we want.
How? Just use
--just add "EXPLAIN" before query EXPLAIN SELECT * FROM member WHERE Id=1;
- SIMPLE : simple SELECT (not use UNION or SUB QUERY)
- 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
Number of records of query
For example there is 50,000 records now, and rows is 45780, so almost 92% records SQL searched.
▼ 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;
- Btree: It is Balanced tree, one of the searching algorithms. It is similar to Binary tree
Actually setting primary key is the same as setting index, and faster than normal Index, so you should check it at first
it is also setting unique keys means using index
Primary key > Unique key > index
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