db-index

What’s indexing in database

tags: learning database

content

• it’s just like the index of a phone book:

A 
- Alex
B
- Bob
- Bill

• it’s not necessary for databases, a db can function without indexing

• it’s a way to make record lookup fast

• index can be created on a key

  • creating index idx_1 on column last_name in table person:
  • CREATE INDEX idx_1 ON person (last_name)

• index can be created with multiple columns too

  • CREATE INDEX idx_2 on person (last_name, first_name)

• internally, indexes are organized in the form of trees

  • B Tree is the most common one?

Note

database indexing is basically taking one or multiple columns from a table and organizing them in a separate data structure (usually b tree) for faster retrieval, sorting, and searching.
The index stores value and a pointer to the actual rows in the table

up

• b-tree

down

reference