MySQL Tutorials - Herong's Tutorial Examples - v4.43, by Herong Yang
Unique Index on InnoDB Table
This section provides a tutorial example to show the performance impact of unique index on InnoDB tables.
In the last tutorial, I noticed that retrieving a row with SECOND index on a 6400-row InnoDB table requires reading 10 pages, instead of 4 pages as I expected.
So I did more tests as presented below:
1. Review SECOND index created in the last tutorial. I see that it was defined as "Non-unique" and "Nullable". This is could be the reason for reading 10 pages instead of 4 pages.
mysql> show index from Body; | Non_unique | Key_name | Column_name | Null | Index_type | +------------+----------+-------------+------+------------+ | 0 | PRIMARY | id | | BTREE | | 1 | SECOND | fid | YES | BTREE |
2. Create another secondary index called THIRD and declared as unique.
mysql> create UNIQUE index THIRD on Body (fid); mysql> show index from Body; | Non_unique | Key_name | Column_name | Null | Index_type | +------------+----------+-------------+------+------------+ | 0 | PRIMARY | id | | BTREE | | 1 | SECOND | fid | YES | BTREE | | 0 | THIRD | fid | YES | BTREE |
3. Compare page usages on retrieving rows with SECOND index and THIRD index. USE INDEX clause on the SELECT statement specifies which index to use.
mysql> select * from Body USE INDEX (SECOND) where fid=6400; | id | fid | b1 | b2 | b3 | ... +------+------+------+------+------+-... | 6400 | 6400 | B1 | B2 | B3 | ... mysql> call GetReads(); | @read | @last | +-------+----------+ | 10 | 71029792 | mysql> select * from Body USE INDEX (THIRD) where fid=6400; | id | fid | b1 | b2 | b3 | ... +------+------+------+------+------+-... | 6400 | 6400 | B1 | B2 | B3 | ... mysql> call GetReads(); | @read | @last | +-------+----------+ | 5 | 71029797 |
The output confirms that.
Conclusion: whenever possible, create UNIQUE index to improve performance for single-value lookups.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
►InnoDB Storage Engine - Primary and Secondary Indexes
Primary Key Index on InnoDB Table
InnoDB Primary Key Index on Large Table
"SELECT COUNT(*)" on InnoDB Table
Secondary Index on InnoDB Table
Performance of Index Range on InnoDB Table
Workaround on InnoDB "SELECT COUNT(*)" Problem
Performance Tuning and Optimization
Installing MySQL Server on Linux