0

I have 2 tables which are in same database and both have around 100 million rows. The select count(*) statement for faster_table takes around 1 minutes, whereas the slower_table takes around 6 minutes. What could contribute the performance difference? Both queries are run with database under similar load.

CREATE TABLE `faster_table` (
  `id_faster` bigint(20) NOT NULL AUTO_INCREMENT,
       -- many other numeric columns
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1688448378379177985 DEFAULT CHARSET=utf8

CREATE TABLE `slower_table` (
  `id_slower` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
       -- many other varchar columns
  PRIMARY KEY (`id`) COMMENT '主键'
) ENGINE=InnoDB AUTO_INCREMENT=119914501 DEFAULT CHARSET=utf8mb4
Qinjie
  • 1,786
  • 2
  • 19
  • 32
  • Please provide `SHOW TABLE STATUS LIKE '...'` for each -- to see sizes. And `SHOW CREATE TABLE` -- to see indexes. – Rick James Aug 07 '23 at 21:26

0 Answers0