Incredibly slow count(*) on MariaDB / MySQL

Question

Running EXPLAIN SELECT COUNT(*) FROM activities shows it plans to use the secondary index with and only one column int and keylen of 5. Action takes anywhere from 2 minutes to 6 minutes, sporadically

Background

• MariaDB - version 10.11.7
• Partitioned InnoDB table by date-range
• 600M records in the table
• Table size
• Regular operations take reasonable time i.e. up to 2-3 minutes
• 3 indices defined, none on text, all BTREE -
  • Primary - int(11) + datetime, index size is ~30% of the table size
  • Index-1, nullable, non unique - index int(11), size is ~15% of the table size
  • Index-1, nullable, non unique - index int(11), size is ~15% of the table size
• Partition is monthly i.e. PARTITION BY RANGE COLUMNS(activity_datetime)
• Isolation level was set REPEATABLE-READ (it was changed after the problem occurred hence this isn't the cause, but neither the solution)
• Standalone server with 64GB RAM, solely serves the DB
• innodb_buffer_pool_size set to 50GB
• innodb_buffer_pool_chunk_size set to 1GB
• innodb_read_io_threads set to 8 (changed from default 4)
• SHOW ENGINE INNODB STATUS - shows nothing special, deadlock rarely happens, and for sure isn't the cause

Attempted changes:

• Set the Isolation level to REPEATABLE-READ
• count(id) instead of count(*)
• Adjusted system variables, mainly -
  • Increased innodb_buffer_pool_size to 50GB
  • Increased innodb_buffer_pool_chunk_size to 2GB
  • Increased innodb_read_io_threads to 8
• Tested the same on a table copy with indices
• Tested the same on a table copy with no partition usage

Afraid nothing helped, count(*) is super slow, and 99.99% is spent on 'sending data'

1   Starting           28 µs
2   Checking Permissions   6 µs
3   Opening Tables         23 µs
4   After Opening Tables.  15 µs
5   System Lock            18 µs
6   Table Lock             4 µs
7   Init                   7 µs
8   Optimizing             4 µs
9   Statistics             19 µs
10  Preparing              26 µs
11  Executing              2 µs
**12    Sending Data           358.4 s**
13  End Of Update Loop.    20 µs
14  Query End              3 µs
15  Commit                 6 µs
16  Closing Tables         4 µs
17  Unlocking Tables       2 µs
18  Closing Tables         15 µs
19  Starting Cleanup       3 µs
20  Freeing Items          6 µs
21  Updating Status        14 µs
22  Reset For Next Command 4 µs

Create Table

CREATE TABLE `contact_activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_list` int(11) DEFAULT NULL,
  `activity` varchar(50) DEFAULT NULL,
  `id_contact` int(11) DEFAULT NULL,
  `id_isp` int(11) DEFAULT NULL,
  `id_esp` int(11) DEFAULT NULL,
  `id_esp_connection` int(11) DEFAULT NULL,
  `id_message` int(11) DEFAULT NULL,
  `id_campaign` int(11) DEFAULT NULL,
  `activity_datetime` datetime NOT NULL,
  `ip` varchar(150) DEFAULT NULL,
  `country_code` varchar(50) DEFAULT NULL,
  `browser` varchar(50) DEFAULT NULL,
  `os` varchar(50) DEFAULT NULL,
  `data` text DEFAULT NULL,
  `id_platform` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`,`activity_datetime`),
  KEY `id_contact` (`id_contact`),
  KEY `id_list` (`id_list`)
) ENGINE=InnoDB AUTO_INCREMENT=629997644 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE  COLUMNS(`activity_datetime`)
(PARTITION `pMonth_231` VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_232` VALUES LESS THAN ('2023-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_233` VALUES LESS THAN ('2023-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_234` VALUES LESS THAN ('2023-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_235` VALUES LESS THAN ('2023-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_236` VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_237` VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_238` VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_239` VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2310` VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2311` VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2312` VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_241` VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_242` VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_243` VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_244` VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_245` VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_246` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_247` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_248` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_249` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2410` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2411` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMonth_2412` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `pMaxMonth` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) 

Answer 1

key = id_contact key_len = 5 -- This is to be expected. It means that is is doing a full index scan on KEY(id_contact), which is the best it can do for getting COUNT(*).

Requesting COUNT(id) would check each id for not-null and be slightly faster.