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

• MySQL Official Docs
• MySQL Book 
• Structures in InnoDB

Thanks