Mastering the Art of Indexing

Performance Tuning and Benchmarks
Location: Ballroom A
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

DeNA

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.

Comments

04/22/2009 12:20pm 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 scordesse@oreilly.com

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@ oreilly.com

Press and Media

For media-related inquiries, contact Maureen Jennings at maureen@oreilly.com

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.