Mastering the Art of Indexing

Performance Tuning and Benchmarks
Location: Ballroom A Level: Intermediate
Average rating: ****.
(4.71, 7 ratings)

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.

Photo of Yoshinori Matsunobu

Yoshinori Matsunobu


Yoshinori Matsunobu is a Senior Consultant at MySQL, mainly engaging into consulting services in Japan and Asian countries. He have written five MySQL books, having broad-ranging MySQL technical experties, especially DBA, performance tuning, high availability, i18n and MySQL Cluster.

Comments on this page are now closed.


04/22/2009 5:20am PDT

Excellent session! Thank you Yoshinori.

Co-presented By:

O'Reilly Media MySQL/Sun Microsystems
  • Kickfire
  • Virident
  • Infobright, Inc
  • JasperSoft
  • Intel
  • Advanced Micro Devices
  • BIRT Exchange by Actuate
  • Calpont
  • Canonical
  • Continuent
  • Dolphin Interconnect Solutions
  • Facebook
  • HiT Software, Inc.
  • IBM
  • iDashboards
  • Oracle
  • Pentaho
  • R1Soft
  • Schooner Information Technology
  • SQLstream
  • Ticketmaster
  • Zmanda, Inc.
  • Linux Journal

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at

Download the MySQL Sponsor/Exhibitor Prospectus

Media Partner Opportunities

Download the Media & Promotional Partner Brochure (PDF) for information on trade opportunities with O'Reilly conferences or contact mediapartners@

Press and Media

For media-related inquiries, contact Maureen Jennings at

MySQL Conference Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the MySQL Conference newsletter.

Contact Us

View a complete list of MySQL contacts.