"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

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 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

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 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

 InnoDB Table with Long Rows

 Secondary Index on InnoDB Table

 Unique Index on InnoDB Table

 Performance of Index Range on InnoDB Table

 Workaround on InnoDB "SELECT COUNT(*)" Problem

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB