MySQL Indices
A Tale of 2 Weeks
Agenda
- Database Index Basics ( the What? and the Why? ).
- B Trees.
- Choosing Indexes.
- MySQL Query Optimisation Tricks.
- Learnings .
BOOK InDEX
Database Index
What ?
Data Structure that improves the speed of data retrieval operations on database table.
- Wikipedia
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.
Advantages
Faster Lookups
Lesser Disk I/O
Uniqueness Constraints
Text Searching
Disadvantages
Slower Creates and Updates
More Disk Space
References
Thanks
MySQL Indices
By Rajeev Bharshetty
