bbang
bbang

Reputation: 11

Why does Innodb_buffer_pool_read_requests increase by 400 for a simple indexed query?

I am monitoring the Innodb_buffer_pool_read_requests before and after executing my query on a MySQL, and I noticed that it increases by approximately 400, even though my query is very simple.

Here is my query:

SELECT *  
FROM my_table t  
WHERE t.a = 20000  
LIMIT 1;  

The column a has an index, and the query is confirmed to use the index correctly. Below is the status difference before and after executing the query:

┌──────────────────────────────────┬────────┐
│ (index)                          │ Values │
├──────────────────────────────────┼────────┤
│ Innodb_buffer_pool_read_requests │ 400    │
│ Innodb_buffer_pool_reads         │ 0      │
│ Handler_read_first               │ 0      │
│ Handler_read_key                 │ 1      │
│ Handler_read_last                │ 0      │
│ Handler_read_next                │ 0      │
│ Handler_read_prev                │ 0      │
│ Handler_read_rnd                 │ 0      │
│ Handler_read_rnd_next            │ 0      │
└──────────────────────────────────┴────────┘

I don’t understand why Innodb_buffer_pool_read_requests increases by 400 when Handler_read_key only increases by 1. This behavior is consistent across multiple executions.

Can someone explain why this happens?


November 28 Update

Here is my table schema:

CREATE TABLE my_table
(
    id         INT UNSIGNED                       NOT NULL
        PRIMARY KEY,
    a          INT UNSIGNED                       NOT NULL,
    b          INT UNSIGNED                       NOT NULL,
    contents   VARCHAR(255)                       NOT NULL,
    deleted_at DATETIME                           NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX ix_a
    ON my_table (a);

CREATE INDEX ix_b
    ON my_table (b);

I am running MySQL version 8.0.40 in a Docker container. No configurations have been modified from the default. It is running on my local PC, and I am the only user.

Upvotes: 0

Views: 55

Answers (0)

Related Questions