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.
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.
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.
For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at firstname.lastname@example.org
For media-related inquiries, contact Maureen Jennings at email@example.com
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).
View a complete list of O'Reilly MySQL Conference contacts.