Mastering the Art of Indexing

Everybody knows that RDBMS supports indexes.

Everybody knows that indexes can boost SELECT performance.

Many people know that indexes degrades INSERT/UPDATE/DELETE performance.

How do you verify that you are using an index correctly?

How do you estimate how badly INSERT/UPDATE/DELETE are slowed down by indexes?

In this session, I’ll explain detailed mechanism of traditional MySQL B+Tree indexes, optimizer plans and how to boost performance. The following topics will be covered.

• How to speed up SELECT
  • Internal mechanism
  • Single B+Tree index
  • Multi-column index
  • index-merge
  • Index-only read (covering index)
  • How to estimate how many i/o will happen

• How to speed up INSERT/UPDATE/DELETE
  • Internal mechanism (how indexes are fragmented)
  • Sequential inserts vs random order inserts
  • Practical architecture designs
  • Relationships with I/O scheduler settings

Cardinality and Insert ordering are also covered. Suppose you have an product_order table (order_id, order_date, product_id,...) and having indexes on these three columns. order_id is an auto_increment, order_date is inserted by sorted order, product_id is sorted by random order. How these indexes affect performance? How different between storage engines? I’ll show benchmarking results and explain under the hood. I also cover relationships between Operating system and MySQL index implementation. You should be interested when you realize that Linux i/o scheduler affects index performance.