Question 247986 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Slow query with 3 seconds of execution

Using FreeBSD freebsd 13.2-RELEASE-p2 FreeBSD 13.2-RELEASE-p2 GENERIC amd64 as ec2 instace(t2.xlarge) and mariadb104-server-10.4.28: mysql Ver 15.1 Distrib 10.5.20-MariaDB, for FreeBSD13.2 (amd64) using EditLine wrapper

Trying to figure out why query:

select v.id, b.book_number, b.title, v.question_ocr 
from version v             
join solution s on v.solution_id = s.id             
join assignment a on s.assignment_id = a.id   
join book b on a.book_id = b.id           
order by v.created_at desc limit 10;

is takes over 3 sec to execute.

Explain shows:

+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+
| id   | select_type | table | type   | possible_keys                | key                  | key_len | ref                   | rows | Extra                                                     |
+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | a     | index  | PRIMARY,IDX_30C544BA16A2B381 | IDX_30C544BA16A2B381 | 145     | NULL                  | 1344 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | b     | eq_ref | PRIMARY                      | PRIMARY              | 144     | db_name____.a.book_id | 1    |                                                           |
|    1 | SIMPLE      | s     | ref    | PRIMARY,IDX_9F3329DBD19302F8 | IDX_9F3329DBD19302F8 | 145     | db_name____.a.id      | 35   | Using index                                               |
|    1 | SIMPLE      | v     | ref    | IDX_BF1CD3C31C0BE183         | IDX_BF1CD3C31C0BE183 | 145     | db_name____.s.id      | 1    |                                                           |
+------+-------------+-------+--------+------------------------------+----------------------+---------+-----------------------+------+-----------------------------------------------------------+

The funny thing is that when I remove order by clause, it takes 0.0001 sec to execute.

I tried profiling tools like vmstat, iostat, top, netstat.

Does any have any solution on how can I fix the slow query issue?

There is an index on the created_at column.

Describe of version:

+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| id                  | char(36)     | NO   | PRI | NULL    |       |
| solution_id         | char(36)     | YES  | MUL | NULL    |       |
| status              | varchar(255) | NO   |     | NULL    |       |
| created_at          | datetime     | NO   | MUL | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

Solution:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | char(36)     | NO   | PRI | NULL    |       |
| book_id          | char(36)     | YES  | MUL | NULL    |       |
| exercise_number  | varchar(255) | NO   |     | NULL    |       |
| created_at       | datetime     | NO   |     | NULL    |       |
| assignment_id    | char(36)     | YES  | MUL | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

Assignment:

+--------------------------------+--------------+------+-----+---------+-------+
| Field                          | Type         | Null | Key | Default | Extra |
+--------------------------------+--------------+------+-----+---------+-------+
| id                             | char(36)     | NO   | PRI | NULL    |       |
| book_id                        | char(36)     | YES  | MUL | NULL    |       |
| price                          | double       | YES  |     | NULL    |       |
| created_at                     | datetime     | YES  |     | NULL    |       |
+--------------------------------+--------------+------+-----+---------+-------+

Book:

+--------------------------+--------------+------+-----+---------+-------+
| Field                    | Type         | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| id                       | char(36)     | NO   | PRI | NULL    |       |
| title                    | varchar(255) | NO   |     | NULL    |       |
| book_number              | varchar(255) | NO   | UNI | NULL    |       |
+--------------------------+--------------+------+-----+---------+-------+

Index for version:

+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                   | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| version |          0 | PRIMARY                    |            1 | id                  | A         |      223710 |     NULL | NULL   |      | BTREE      |         |               |
| version |          1 | IDX_BF1CD3C31C0BE183       |            1 | solution_id         | A         |      223710 |     NULL | NULL   | YES  | BTREE      |         |               |
| version |          1 | idx_solution_id_created_at |            1 | solution_id         | A         |      223710 |     NULL | NULL   | YES  | BTREE      |         |               |
| version |          1 | idx_solution_id_created_at |            2 | created_at          | A         |      223710 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Solution:

+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| solution |          0 | PRIMARY              |            1 | id               | A         |       91914 |     NULL | NULL   |      | BTREE      |         |               |
| solution |          1 | IDX_9F3329DB16A2B381 |            1 | book_id          | A         |         666 |     NULL | NULL   | YES  | BTREE      |         |               |
| solution |          1 | IDX_9F3329DBD19302F8 |            1 | assignment_id    | A         |        2872 |     NULL | NULL   | YES  | BTREE      |         |               |
| solution |          1 | IDX_9F3329DB4B09E92C |            1 | administrator_id | A         |         154 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Assignment:

+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| assignment |          0 | PRIMARY               |            1 | id                         | A         |        1431 |     NULL | NULL   |      | BTREE      |         |               |
| assignment |          1 | IDX_30C544BA16A2B381  |            1 | book_id                    | A         |         715 |     NULL | NULL   | YES  | BTREE      |         |               |
| assignment |          1 | IDX_30C544BA4B09E92C  |            1 | administrator_id           | A         |         130 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Book:

+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | PRIMARY               |            1 | id          | A         |        2698 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | IDX_CBE5A33123EDC87   |            1 | subject_id  | A         |          42 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

my.cnf for mariadb:

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
bind-address = 127.0.0.1
socket = /var/run/mysql/mysql.sock
innodb_print_all_deadlocks = ON
collation_server = utf8_unicode_ci
character_set_server = utf8


innodb_buffer_pool_size = 8G  
performance_schema = ON       
innodb_thread_concurrency = 10 
max_connections = 150         
innodb_flush_log_at_trx_commit = 2  
join_buffer_size = 512K       
key_buffer_size = 128M         
sort_buffer_size = 1M
Tags:

2

Answers


  1. 0

    Did you try changing the index in created_at to be sorted DESC instead of ASC?
    You can read more about this here:
    https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

    Login or Signup to reply.
  2. 0 
    select  v2.id, b.book_number, b.title, v3.question_ocr
    from  ( SELECT id, solution_id 
              FROM version v1 ORDER BY created_at DESC LIMIT 10
          ) v2
    join  solution s  ON v2.solution_id = s.id
    join  assignment a  ON s.assignment_id = a.id
    join  book b  ON a.book_id = b.id
    JOIN  version v3 ON v3.id = v2.id
    order by  v3.created_at desc   -- yes, repeated (if needed)
    ;

    And have

    INDEX(created_at, solution_id, id)

    char(36) — Also, consider avoiding UUIDs; see UUIDs

    And, what is the value of innodb_buffer_pool_size? It should be about 70% of available RAM.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search