St\u00e9phane Combaudon of Dailymotion.<\/p>\n
Index: separate data structure to speed up SELECTs. Think of index in a book. In MySQL, key=index. Consider that indexes are trees.<\/p>\n
InnoDB’s clustered index – data is stored with the Primary Key (PK) so PK lookups are fast. Secondary keys hold the PK values. Designing InnoDB PK’s with care is critical for performance.<\/p>\n
An index can filter and\/or sort values. An index can contain all the fields needed for the query you don’t need to go to the table (a covering index).<\/p>\n
MySQL only uses 1 index per table per query (not 100% true – OR clauses), so think of a composite index when you can. Can’t index TEXT fields (use a prefix). Same for BLOBs and long VARCHARs.<\/p>\n
Indexes: speed up queries, increases the size of your dataset, slows down writes. How big is the write slowdown? Simple test by Stephane, for in-memory workloads he says adding 2 keys makes performance 2x worse; for on-disk workloads he says its 40x worse. Never neglect the slowdown of your writes when you have an index. There is a graph in the slidedeck.<\/p>\n
What is a bad index? Unused indexes. Redundant indexes. Duplicate indexes.<\/p>\n
Indexing is not an exact science, but guessing is probably not the best way to design indexes. Always check your assumptions – EXPLAIN does not tell you everything, time your queries with different index combinations, SHOW PROFILES is often valuable. Slow query log is a good place to start.<\/p>\n
Many slides with examples, so I hope Stephane posts the deck soon. If possible, try to sort & filter (an index is not always the best for sorting).<\/p>\n
InnoDB’s clustered index is always covering. SELECT by PK is the fastest access with InnoDB.<\/p>\n
An index can give you 3 benefits: filtering, sorting, covering.<\/p>\n