Home arrow MySQL arrow Page 3 - Optimizing for Query Speed

13.1.2 Obtaining Table Index Information - 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

To find out what indexes a table has, use SHOW CREATE TABLE to display the CREATE TABLE statement that corresponds to the table structure, including its indexes.

For more detailed information about the indexes, use SHOW INDEX. For example, SHOW INDEX produces the following output for the Country table of the world database:

mysql> SHOW INDEX FROM Country\G
********************* 1. row **********************
Table: Country
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Code
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

The output indicates that the table has a single index, a primary key on the Code column. The output for the City table is similar except that it indicates the ID column is the primary key:

mysql> SHOW INDEX FROM City\G
********************* 1. row **********************
Table: City
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

For the CountryLanguage table, the output has two rows because the primary key includes two columns, Country and Language:

mysql> SHOW INDEX FROM CountryLanguage\G
********************* 1. row ***************************
Table: CountryLanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Country
Collation: A
Cardinality: NULL
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: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

The Seq_in_index values show the order of the columns within the index. They indicate that the primary key columns are Country first and Language second. This information corresponds to the following PRIMARY KEY declaration:

PRIMARY KEY (Country, Language)


 
 
>>> 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: