MySQL Indices

A Tale of 2 Weeks


  • Database Index Basics ( the What? and the Why? ).
  • B Trees. 
  • Choosing Indexes.
  • MySQL Query Optimisation Tricks.
  • Learnings .


Database Index

What ?

Data Structure that improves the speed of data retrieval operations on database table.


Why ?

  • Quickly Locate Data
  • Faster Lookups

select * from PHOTOS where USER_id = 1;

Faster Lookup, How ?

B Trees

B Trees

Choosing Indexes

Index columns that you use for searching, sorting, or grouping.

Column Cardinality

Take advantage of leftmost prefixes.

Don't over-index.

Match index types to the type of comparisons you perform. 

(Hash based/ B-tree indexing)

MySQL Query Optimisation

Try to compare columns that have the same data type

Try to make indexed columns stand alone in comparison expressions

Don't use wildcards at the beginning of a LIKE pattern

Use EXPLAIN to verify optimizer operation.


Faster Lookups

Lesser Disk I/O

Uniqueness Constraints

Text Searching


Slower Creates and Updates

More Disk Space



MySQL Indices

By Rajeev Bharshetty

MySQL Indices

  • 835
Loading comments...

More from Rajeev Bharshetty