0

I have a table with more than a million records. I get the data by using LIMIT, but also ORDER BY. The latter causes a huge performance problem.

My query ends with this:

... ORDER BY `record`.`timestamp` DESC LIMIT 5;

Executing this query takes more than 20s. If I'll remove ORDER BY, the execution time in mysql is 0.00 which is obviously a huge difference. I know why it happens - the DB needs to sort the data first, to apply LIMIT on it. I've noticed while profiling that the Creating sort index step takes a lot of time, so I've decided to create an index like that:

alter table record add index timestampidx (timestamp desc);

Unfortunately, it didn't change anything - the execution time is exactly the same as it was. The database I'm running is locally spawned by docker-compose with the following configuration:

version: '3.9'
services:
  db:
    image: mysql:8
    ports:
      - "3306:3306"
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: db-password
    volumes:
      - ./data:/var/lib/mysql

What am I doing wrong? What should I do to make my query faster?

Edit:

The create statement:

CREATE TABLE `record` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `thread` int NOT NULL,
  `timestamp` timestamp NOT NULL,
  `pass` int DEFAULT NULL,
  `fail` int DEFAULT NULL,
  `cyc_lmt` int DEFAULT NULL,
  `diag_err` int DEFAULT NULL,
  `not_run` int DEFAULT NULL,
  `pass_cyc` int DEFAULT NULL,
  `eff` float DEFAULT NULL,
  `cpu_lmt` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_index` (`name`,`thread`),
  KEY `thread` (`thread`),
  KEY `timestampidx` (`timestamp` DESC),
  CONSTRAINT `record_ibfk_1` FOREIGN KEY (`thread`) REFERENCES `thread` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4006386 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The full query:

    SELECT  `record`.`id`, `record`.`name`, `record`.`thread`, `record`.`timestamp`,
            `record`.`pass`, `record`.`fail`, `record`.`cyc_lmt`,
            `record`.`diag_err`, `record`.`not_run`, `record`.`pass_cyc`,
            `record`.`eff`, `record`.`cpu_lmt`,
            `thread`.`id`, `thread`.`name`, `thread`.`config`,
            `config`.`id`, `config`.`name`, `config`.`regression`,
            `regression`.`id`, `regression`.`name`, `regression`.`instance`,
            `instance`.`id`, `instance`.`name`
        FROM  (`record`
        INNER JOIN  (`thread`
        INNER JOIN  (`config`
        INNER JOIN  (`regression`
        INNER JOIN  `instance`  ON `regression`.`instance` = `instance`.`id`
                    )  ON `config`.`regression` = `regression`.`id`
                    )  ON `thread`.`config` = `config`.`id`
                    )  ON `record`.`thread` = `thread`.`id`
              )
        ORDER BY  `record`.`timestamp` DESC
        LIMIT  5;

Execution plan with ORDER BY:

+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys      | key        | key_len | ref                       | rows | filtered | Extra                                        |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | instance   | NULL       | index | PRIMARY            | name       | 258     | NULL                      |   14 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | regression | NULL       | ref   | PRIMARY,instance   | instance   | 4       | regressions.instance.id   |  125 |   100.00 | NULL                                         |
|  1 | SIMPLE      | config     | NULL       | ref   | PRIMARY,regression | regression | 4       | regressions.regression.id |    7 |   100.00 | NULL                                         |
|  1 | SIMPLE      | thread     | NULL       | ref   | PRIMARY,config     | config     | 4       | regressions.config.id     |   10 |   100.00 | NULL                                         |
|  1 | SIMPLE      | record     | NULL       | ref   | thread             | thread     | 4       | regressions.thread.id     |    8 |   100.00 | NULL                                         |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+----------------------------------------------+

Execution plan without ORDER BY:

+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys      | key        | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | instance   | NULL       | index | PRIMARY            | name       | 258     | NULL                      |   14 |   100.00 | Using index |
|  1 | SIMPLE      | regression | NULL       | ref   | PRIMARY,instance   | instance   | 4       | regressions.instance.id   |  125 |   100.00 | NULL        |
|  1 | SIMPLE      | config     | NULL       | ref   | PRIMARY,regression | regression | 4       | regressions.regression.id |    7 |   100.00 | NULL        |
|  1 | SIMPLE      | thread     | NULL       | ref   | PRIMARY,config     | config     | 4       | regressions.config.id     |   10 |   100.00 | NULL        |
|  1 | SIMPLE      | record     | NULL       | ref   | thread             | thread     | 4       | regressions.thread.id     |    8 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+--------------------+------------+---------+---------------------------+------+----------+-------------+
3
  • Are you sure that the index is created? Also, do you see it used if you try a simple SELETC r.* FROM record AS r ORDER BY r.timestamp DESC LIMIT 5; Jul 9, 2021 at 11:09
  • Huh, it seems that it started working. I've tried to test a simple query and the result was instant. I've then used the original one, and again - instant result. The Creating sort index step is gone from profiling. Is it possible that the database needed some time to finish setting up the index? By that time I mean like a few hours.
    – Djent
    Jul 9, 2021 at 17:44
  • Can you provide table structure for regression table ? Jul 10, 2021 at 4:34

2 Answers 2

0

Taking into account the parenthesis in FROM clause the query is by fact

SELECT ...
FROM       `instance`
INNER JOIN `regression` ON `regression`.`instance` = `instance`.`id`
INNER JOIN `config`     ON `config`.`regression` = `regression`.`id`
INNER JOIN `thread`     ON `thread`.`config` = `config`.`id`
INNER JOIN `record`     ON `record`.`thread` = `thread`.`id`
ORDER BY `record`.`timestamp` DESC LIMIT 5;

The index by record (timestamp ASC/DESC) cannot be used for this query improvement.

Recommendation - use some WHERE clause by record.timestamp which selects a small part of rows but not less than 5 ones with guarantee. For example, WHERE record.timestamp > CURRENT_DATE - INTERVAL 1 DAY. And try to create an index record (timestamp, thread).

1
  • I can't add WHERE clause, as I don't know how old is the oldest record I need. Isn't there another way to eliminate the Creating sort index step and use predefined index instead?
    – Djent
    Jul 9, 2021 at 10:39
0

I don't think I have ever seen JOINs parenthesized like that. Suggest you change to @Akina's way -- without parentheses. That may even fix the performance problem.

If not, then turn the query inside-out:

SELECT ...
    FROM ( SELECT ... 
              FROM record
              ORDER BY timestamp DESC
              LIMIT 5
        ) AS r
    INNER JOIN ... ON ...
    INNER JOIN ... ON ...
    INNER JOIN ... ON ...
    INNER JOIN ... ON ...
    ORDER BY r.timestamp DESC   -- yes, again

However, this may fail to help. It depends on whether the JOINs expand the number of rows, thereby invalidating the "5" that you started with.

And, yes, replace INDEX(timestamp DESC) with a 'covering' INDEX(timestamp, thread). (The DESC should not matter on any version of MySQL -- in this case.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.