Saturday, July 20, 2024

Understanding MySQL Indexes

The correct implementation of indexes is critical to improving SQL performance in any RDBMS. However, there are some basic problems regarding how MySQL and MySQL Storage Engines implement indexes in relation to other RDBMS products including Oracle.

In this presentation attendees will learn how to identify and verify MySQL index usage, what essential MySQL Tools exists for determining how to create better indexes and be able to verify the results of improvements. Understanding how the optimizer chooses indexes, generally only one index per table join and also the exceptions. Learn about advanced topics including covering indexes and partial column/prefix column indexes.

There will also be specific examples of how indexes differ between the popular MySQL storage engines including InnoDB, MyISAM, Memory and other available third party storage engines.

The MySQL database, a recent acquisition of Oracle is commonly found with the Oracle RDBMS in many organizations. While MySQL is a relational database there are some fundamental differences in how indexes are best used in MySQL. Techniques used by Oracle Architects and DBAs do not always result in the most optimal possible solution.

See also the “Explaining the MySQL EXPLAIN” presentation.

This presentation is being delivered in 2011. Check back for dates and locations.