Performance of a simple MySQL query

Asked Apr 25 '23 at 17:02

Active Apr 25 '23 at 17:23

Viewed 21 times

I have an index on a table in MySQL:

| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 2011  |          1 | ix_2011_index |            1 | index       | A         |      292691 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL

When I try to see how many rows there are in the table,

SELECT COUNT(*) from mytable.2011;

It takes forever. Granted, there are tens of millions of rows in the table, but each row is indexed.

Shouldn't this be almost instantaneous?

I created the table with the default engine:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

edited Apr 25 '23 at 17:23

asked Apr 25 '23 at 17:02

Ivan

7,448
14
69
134

Is your table using the InnoDB storage engine (this is the default storage engine on MySQL)? – Bill Karwin Apr 25 '23 at 17:06
InnoDB doesn't cache row counts. – Barmar Apr 25 '23 at 17:06
Possible duplicate of https://stackoverflow.com/q/57921400/20860 – Bill Karwin Apr 25 '23 at 17:06
@BillKarwin, I didn't tell MySQL which engine to use on installation, so assuming the default – Ivan Apr 25 '23 at 17:09
You can verify with `SHOW CREATE TABLE mytable.2011`, and it will show the ENGINE option on the last line. But it's probably InnoDB if you didn't specify. – Bill Karwin Apr 25 '23 at 17:11
@BillKarwin see edited original post. It is InnoDB. – Ivan Apr 25 '23 at 17:24
Okay, that is expected. See the linked answer for the gory details, but the short answer is that SELECT COUNT(*) on a large InnoDB table is not instantaneous. – Bill Karwin Apr 25 '23 at 17:29

Performance of a simple MySQL query

0 Answers0