Important things every developer need to know about Database Indexing

Assuming you know the basics of Database and know the definition of indexes used in DB, here are some important things about Database indexes about which every good developer must aware of:

1. DB Indexing is used to read data faster, but it makes writing slower.
2. Indexes use in-memory data structures.
3. Indexes use B-Tree (Balanced Tree) data structure to store data and use Binary search for reading/searching.
4. It always saves corresponding DB internal row-id (not table PK) with every indexed node in the B-tree
5. It is not good to add an index to each column of a table.
6. If you have applied index on certain column but in query use any function on an indexed column, the index will not help at all.
7. You can create a combined index on multiple columns. Order matters as per query need.
8. To understand the use of indexes or to know if the query is actually taking the benefit of your index on your column use Explain.
9. Explain give you the execution plan of the query.
10. Explain give you info about what type of scan it uses while using indexes. It can be
    
    •    Conts/EQ_Ref (Fastest) - use Binary search(on B-Tree) given the where is used on a unique column.
             e.g. where id = 127 (given id is a primary key or unique).
      
    •    Ref / Range - use B-tree doubly-linked leaf scan after finding the start limit record till the end limit record in the index.
             e.g. where created_at between '2018-09-01 00:00:00' and '2019-09-01 00:00:00'.  (given index applied on created_at)
      
    •    Index - scan all index records
             e.g. where created_at between '2018-09-01 00:00:00' and '2019-09-01 00:00:00' and user_id = 127; (given combine index applied on created_at and user_id - here order columns index matters)
      
    •    ALL - scan every column in every row. worst case. It happens when the given index is not useful for the query.
             e.g. where user_id = 127; (given index is on created_at).
      
    So indexing should be always used as per the query need.
    
11. Indexing on a specific column does not always make the query faster having that specific column in where clause.
12. The column in select clause also impacts the performance if it differs from the indexes column it is using in where clause. That is because each time it read a record from the index it read corresponding select column value from memory disk which results more no.of io reads to disks hence execution time.
13. Indexing is always applied as per developer need.

Reference video from Laracon EU. URL: https://youtu.be/HubezKbFL7E