MySQL table queries extremely slow

innodbMySQLmysql-8.0

I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like

CREATE TABLE `measurement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` int(11) DEFAULT NULL,
  `field2` varchar(200) NOT NULL,
  `field3` datetime(6) NOT NULL,
  `field4` double NOT NULL,
  `field5` double NOT NULL,
  `field6` double NOT NULL,
  `field7` double NOT NULL,
  `field8` double NOT NULL,
  `field9` double NOT NULL,
  `field10` double NOT NULL,
  `field11` double NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`field2`),
  KEY `index3` (`field3`)
) ENGINE=InnoDB AUTO_INCREMENT=7435062 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

and queries on it are extremely slow. For example,

select count(*) from measurement;

takes around 10 minutes. I have another table in the same database with roughly the same number of rows and columns where the same query takes 35 ms. When I do an explain on the above query I get

id|select_type|table             |partitions|type |possible_keys|key       |key_len|ref|rows   |filtered|Extra      |
--|-----------|------------------|----------|-----|-------------|----------|-------|---|-------|--------|-----------|
 1|SIMPLE     |measurement       |          |index|             |index3    |8      |   |7132937|   100.0|Using index|

Before I added the other two indices this query took 17 minutes. Everything was created using Django 3.1. When I try to analyze the table the result is

Table      |Op     |Msg_type|Msg_text|
-----------|-------|--------|--------|
measurement|analyze|status  |OK      |

Where do I need to look to figure out what my issue is?

Best Answer

It sounds you have a slow disk, small ram and the main cache is too small.

How much RAM? What is the value of innodb_buffer_pool_size. SSD or HDD?

Without the secondary indexes, it had no choice but to read the entire table which was probably about 200MB? But the buffer pool is only 16MB or 128MB (old defaults), hence "caching" was useless.

Moving to a much smaller secondary index (with 8-byte DATETIME(6) and 4-byte INT) was somewhat faster, but still too big to be cached.

I don't want to advise on the buffer_pool size without first knowing how much (or little) RAM you have and whether there are other processes running.

Related Question