Home arrow MySQL arrow Page 6 - Optimizing for Query Speed

13.1.3.2 Leftmost Index Prefixes - MySQL

Optimzing your queries can help them run more efficiently, which can save a significant amount of time. This article covers index optimization and index usage. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Optimizing for Query Speed
  2. 13.1 Index Optimization and Index Usage
  3. 13.1.2 Obtaining Table Index Information
  4. 13.1.3 Using Indexes
  5. 13.1.3.1 Indexing Column Prefixes
  6. 13.1.3.2 Leftmost Index Prefixes
  7. 13.1.4 FULLTEXT Indexes
By: Sams Publishing
Rating: starstarstarstarstar / 25
August 03, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: