More Mastering the Art of Indexing

Performance Tuning and Benchmarking
Location: Ballroom A Level: Intermediate
Presentation: external link
Average rating: ***..
(3.43, 7 ratings)

At the MySQL Conference and Expo in 2009, I presented Mastering the Art of Indexing. The session was very popular (standing room only and highly evaluated. Thanks audiences!) so this year I’d like to speak about more advanced indexing topics. Don’t worry, this session is not duplicate from the last year’s session at all. The following topics will be covered.

  • Indexes and lock contentions
  • More about Covering Index
  • How indexes impact MySQL optimizer

Indexing strategies should sometimes be considered from lock contention perspectives. Updates by full table scan will lock all records (in InnoDB) so scalability will be terrible. Indexing is important not only from performance point of views, but also to avoid unexpected errors such as deadlocks. Even though all queries are executed in auto committed mode, deadlock errors might happen if indexing strategy is not appropriate.

Covering index is well known indexing strategy and I also explained at the last MySQL conference. This year I will introduce some more practical examples based on a “Blog entry” table. For example, how Covering Index can speed up retrieving a blog title by blog id, LIMIT/OFFSET, counting rows, etc.

If you have two or more indexes within single table, MySQL optimizer chooses one of them. In what case does MySQL not choose the best index?

These issues would have sometimes made you confused. One of my objectives in this talk is to make them as clear as possible, by showing some practical examples and explaining internal mechanisms.

Photo of Yoshinori Matsunobu

Yoshinori Matsunobu


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

  • Oracle
  • Monty Program
  • Calpont
  • Facebook
  • Gear6
  • Infobright, Inc
  • JasperSoft
  • Joyent
  • Kickfire
  • NorthScale, Inc.
  • Percona
  • Schooner Information Technology
  • Solid Quality Mentors (SolidQ)
  • Intel
  • Pentaho
  • Linux Pro Magazine

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at

Download the O'Reilly MySQL Conference & Expo 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

O'Reilly MySQL Conference Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the O'Reilly MySQL Conference newsletter (login required).

Contact Us

View a complete list of O'Reilly MySQL Conference contacts.