Thank god, adding this technique to reform my previous unconcious(?) coding procedure. Knowing indexing is prolly the minimum requirement to become a Senior programmer. I was unconsciously aware of what indexing is, but now it is time to clearify what it is in detail.
Indexing
Indexing is a technique used to improve the performance of database queries by creating data structures, called indexes, that allow for efficient retrieval of data.
General structure of how Database storage the data
According to the above image, the core concept is instead of going all over from the start to the end, ask the data if goes "left" of "right"
- create a table without thinking(무지성 테이블 생성하기)
- adding data
Clustering index
- adding clustering index
- result for before and after "indexing"
- before:
- after:
As you can see, data has been arranged.
Conclusion:
key:
- data is being arranged
- only one in per table
- leaf page = data page
- primary key(priority) or unique+not null
Non-clustering index
-
adding non-clustering index on 'name' column
-
result
key:
- data page is as it is, not changed
- extra index page is created, extra space needed
- multiple in per table
- having 'address' of data page in leaf page
- 'unique constraint' or 'directly adding index'
reference:
https://www.youtube.com/watch?v=edpYzFgHbqs
