MySQL Tutorials - Herong's Tutorial Examples - v4.43, by Herong Yang
Performance of Index Range on InnoDB Table
This section provides a tutorial example to show the performance impact of uniqueness of InnoDB secondary index on searching with index ranges.
In previous tutorials, we have done enough tests on single-value search with on InnoDB indexes.
In this tutorial, let's do more tests on value range search on InnoDB indexes.
1. Compare page usages on retrieving data with single-value search and value range search on primary index.
mysql> select * from Body USE INDEX (PRIMARY) where id=1; mysql> call GetReads(); @read = 2 mysql> select * from Body USE INDEX (PRIMARY) where id>=1 and id<=1; mysql> call GetReads(); @read = 2 mysql> select * from Body USE INDEX (PRIMARY) where id>=1 and id<2; mysql> call GetReads(); @read = 7 mysql> select * from Body USE INDEX (PRIMARY) where id>=1 and id<=2; mysql> call GetReads(); @read = 6
The output shows that:
2. Compare page usages on retrieving data with index ranges on unique secondary index. The performance is about 3.14 page per index value and is growing linearly.
mysql> select * from Body USE INDEX (THIRD) where fid=1; @read = 5 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=1; @read = 5 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=2; @read = 12 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=3; @read = 15 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=4; @read = 18 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=5; @read = 22 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=6; @read = 25 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=7; @read = 28 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=8; @read = 31 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=9; @read = 34 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=10; @read = 37 ... mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=20; @read = 69 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=30; @read = 100 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=30; @read = 131 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=30; @read = 162 ... mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=100; @read = 319 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=200; @read = 631 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=300; @read = 944 mysql> select * from Body USE INDEX (THIRD) where fid>=1 and fid<=400; @read = 1256 ...
3. Compare page usages on retrieving data with index ranges on non-unique secondary index. The performance is about 3.14 page per index value and is growing linearly.
mysql> select * from Body USE INDEX (SECOND) where fid=1; @read = 10 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=1; @read = 10 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=2; @read = 12 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=3; @read = 15 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=4; @read = 19 ... mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=10; @read = 37 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=20; @read = 69 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=30; @read = 100 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=40; @read = 131 ... mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=100; @read = 319 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=200; @read = 631 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=300; @read = 944 mysql> select * from Body USE INDEX (SECOND) where fid>=1 and fid<=400; @read = 1256
It turns out that uniqueness on InnoDB secondary index has no performance impact on searching with index ranges.
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