Index, Index algorithm

✅ DB Index

to search faster in RDMS
enhance query speed

• 👍🏻 data search faster
• 👍🏻 data sort faster
• 👍🏻 data update faster
• 👍🏻 efficient management of data

✔️ index a column in table(save as file)

• do not need to full scan the whole table record
• search index file(B+ Tree)
• search MYI file

✔️ when index is created on a certain column

• sort column data
• saved on seperate memory with data physical address
• save column value with physical address key-value

✔️ When a table is created three files are made

• FRM: table architecture
• MYD: data
• MYI: index info

👎🏻 disadvantages

• .mdb file size increases
• difficult to change one page concurrently
• updating data, inserting or deleting record in an indexed field capability ⬇️
• if frequent update of data, need to rewrite index

When to use Index?

• column frequently used in where
  
• foreign key column
  
• column frequently used in join
  
• data range is broad, but little repetition
  

When not to use Index?

column with high data repetition
column with high DML

✅ Index algorithm

✔️ Hash Table

• save key-value
• however, not frequently used in index
  • bc hash is optimized for =
  • in DB, need more <, >

✔️ B+ Tree