Optimizing for Query Speed - 13.1.3.2 Leftmost Index Prefixes
(Page 6 of 7 )

In a table that has a composite (multiple column) index, MySQL can use leftmost index prefixes of that index. A leftmost prefix of a composite index consists of one or more of the initial columns of the index. MySQL's capability to use leftmost index prefixes enables you to avoid creating unnecessary indexes.

The CountryLanguage table in the world database provides an example of how a leftmost prefix applies. The table has a two-part primary key:

mysql> SHOW INDEX FROM CountryLanguage\G
******************* 1. row ***************************
Table: CountryLanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 246
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
******************* 2. row ***************************
Table: CountryLanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Language
Collation: A
Cardinality: 984
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

The index on the CountryCode and Language columns allows records to be looked up quickly based on a given country name and language. However, MySQL also can use the index given just a country code. Suppose that you want to determine which languages are spoken in France:

SELECT * FROM CountryLanguage WHERE CountryCode =

'FRA';

MySQL can see that CountryCode is a leftmost prefix of the primary key and use it as though it were a separate index. This means there's no need to define a second index on the CountryCode column alone.

On the other hand, if you want to perform indexed searches using just the Language column of the CountryLanguage table, you do need to create a separate index because Language is not a leftmost prefix of the existing index.

Note that a leftmost prefix of an index and an index on a column prefix are two different things. A leftmost prefix of an index consists of leading columns in a multiple-column index. An index on a column prefix indexes the leading bytes of values in the column.

Next: 13.1.4 FULLTEXT Indexes >>
 

More MySQL Articles
More By Sams Publishing

Comments On: Optimizing for Query Speed

· This article is an excerpt from the book "MySQL Certification Guide," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.