MySQL Tutorials - Herong's Tutorial Examples - v4.43, by Herong Yang
Secondary Index on InnoDB Table
This section describes what is secondary index on an InnoDB table. Any any non-primary index is called secondary index.
If you have a foreign key in an InnoDB table, and want to retrieve rows with a given foreign key, you should create a secondary index (non-primary index) on foreign key column.
"Secondary Index" is a term used in the MySQL reference manual. It states that "each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered (primary) index".
Let's continue with the previous tutorial and validate what is stated in the reference manual.
1. Re-create the "Body" table with 6400 rows and add a secondary index called "SECOND" on the "fid" column.
mysql> ... mysql> select count(*) from Body; | 6400 | mysql> show index from Body; | Non_unique | Key_name | Column_name | Null | Index_type | +------------+----------+-------------+------+------------+ | 0 | PRIMARY | id | | BTREE | | 1 | SECOND | fid | YES | BTREE |
2. Verify index/data pages of both PRIMARY and SECOND indexes. They all should be cached in the buffer.
mysql> select index_name, data_size/number_records as record_size, -> count(*) as pages, sum(number_records) as records -> from information_schema.innodb_buffer_page -> where table_name rlike '`Body`' -> group by index_name, data_size/number_records -> order by index_name; | index_name | record_size | pages | records | +------------+-------------+-------+---------+ | PRIMARY | 14.0000 | 1 | 801 | | PRIMARY | 1812.0000 | 801 | 6400 | | SECOND | 14.0000 | 6 | 6400 | | SECOND | 18.0000 | 2 | 12 |
The output shows:
3. Compare page usages on retrieving rows with PRIMARY index and SECOND index.
mysql> set @last = 0; mysql> call GetReads(); mysql> call GetReads(); | @read | @last | +-------+----------+ | 0 | 71010383 | mysql> select * from Body where id=6400; | id | fid | b1 | b2 | b3 | b4 | b5 | b6 | b7 | +------+------+------+------+------+------+------+------+------+ | 6400 | 6400 | B1 | B2 | B3 | B4 | B5 | B6 | B7 | mysql> call GetReads(); | @read | @last | +-------+----------+ | 2 | 71010385 | mysql> select * from Body where fid=6400; | id | fid | b1 | b2 | b3 | b4 | b5 | b6 | b7 | +------+------+------+------+------+------+------+------+------+ | 6400 | 6400 | B1 | B2 | B3 | B4 | B5 | B6 | B7 | mysql> call GetReads(); | @read | @last | +-------+----------+ | 10 | 71010395 |
Okay, InnoDB uses 2 pages: 1 index page and 1 data page, to retrieve the last row with the given PRIMARY key as expected.
But why is InnoDB using 10 pages to retrieve the last row with the given SECOND key? If we follow the secondary key storage logic stated in the MySQL reference manual, InnoDB only need to read 4 pages to retrieve the row:
See the next tutorial on how to optimize the secondary index.
By the way, here are some key words and terminologies used in MySQL documentation:
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