MySQL Tutorials - Herong's Tutorial Examples - v4.43, by Herong Yang
"SELECT COUNT(*)" on InnoDB Table
This section provides a tutorial example showing why 'SELECT COUNT(*)' is slow on InnoDB tables. It does a full table scan by reading all data pages to count the number of rows.
If you have large tables, you probably notices that running "SELECT COUNT(*)" statement on InnoDB tables are slower than MyISAM tables. This is because InnoDB does not store the total number of rows any where. It will need to perform a full table scan to count the number of rows.
Let's continue with the last tutorial and see how InnoDB performs the "SELECT COUNT(*)" statement.
1. Create two custom variables to calculate the number of reads on InnoDB buffer pages.
mysql> set @last = 0; mysql> select variable_value-@last, variable_value -> from performance_schema.session_status -> where VARIABLE_NAME rlike 'Innodb_buffer_pool_read_requests' -> into @read, @last; mysql> select @read, @last; | @read | @last | +----------+----------+ | 58886830 | 58886830 |
2. Run the "SELECT COUNT(*)" statement and see the number of reads.
mysql> select count(*) from Head; | 2000000 | 1 row in set (0.57 sec) mysql> select variable_value-@last, variable_value -> from performance_schema.session_status -> where VARIABLE_NAME rlike 'Innodb_buffer_pool_read_requests' -> into @read, @last; mysql> select @read, @last; | @read | @last | +-------+----------+ | 59152 | 58945982 |
Wow. InnoDB was reading 59152 pages to count the number of rows in the table! If you remember, the table is stored in 2000000/64 = 31250 pages. So InnoDB did more than a full table scan. It scanned the entire table almost twice!
InnoDB must be using a very poor algorithm to count the number of rows. Since the primary key is not nullable and unique, it could just scan all 28 index pages to get the count. With each index page stores 1120 records, 28 index pages cover 31360 page addresses.
3. Someone suggested to add the "USE INDEX" clause to avoid full table scan. But it does not help at all:
mysql> select count(*) from Head USE INDEX (PRIMARY); | 2000000 | mysql> select variable_value-@last, variable_value -> from performance_schema.session_status -> where VARIABLE_NAME rlike 'Innodb_buffer_pool_read_requests' -> into @read, @last; mysql> select @read, @last; | @read | @last | +-------+----------+ | 59152 | 59360046 |
4. Verify the primary key index definition.
mysql> show index from Head \G Table: Head Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 1757022 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL
It looks perfect. The PRIMARY index is unique and not nullable. I have no idea, why InnoDB is not using it to count the number of rows.
The "Cardinality" value, 1757022, is an estimate of unique values in the key. In this case, it is only 88% accurate, a very poor estimate!
By the way, the number of rows maintained in other system tables are also estimations only. For example, the TABLE_ROWS in information_schema.tables table shows 1925789, 96% accurate.
mysql> select * from information_schema.tables where table_name='Head'; | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | DATA_FREE | +------------+----------------+-------------+-----------+ | 1925789 | 243 | 469696512 | 6291456 |
Conclusion: avoid using "SELECT COUNT(*)" statements on InnoDB tables. They are very expensive!
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