Improve slow sql queries

[corentin-soriano]

Steps to reproduce

Slow query :

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'Sleep';
+--------+---------------+-----------------+----------+---------+------+-----------+------------------------------------------------------------------------------+
| ID     | USER          | HOST            | DB       | COMMAND | TIME | STATE     | INFO                                                                         |
+--------+---------------+-----------------+----------+---------+------+-----------+------------------------------------------------------------------------------+
| 105664 | teampass_user | 127.0.0.1:60706 | teampass | Query   |    1 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834246' |
| 105656 | teampass_user | 127.0.0.1:61026 | teampass | Query   |    4 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834243' |
| 105640 | teampass_user | 127.0.0.1:60882 | teampass | Query   |    0 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834247' |
| 105650 | teampass_user | 127.0.0.1:60966 | teampass | Query   |    5 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834242' |
| 105634 | teampass_user | 127.0.0.1:63636 | teampass | Query   |    7 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834240' |
| 105643 | teampass_user | 127.0.0.1:60906 | teampass | Query   |    9 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834238' |
| 105628 | teampass_user | 127.0.0.1:63576 | teampass | Query   |   10 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834237' |
| 105653 | teampass_user | 127.0.0.1:60984 | teampass | Query   |    5 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834242' |
| 105637 | teampass_user | 127.0.0.1:60846 | teampass | Query   |    2 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834245' |
| 105661 | teampass_user | 127.0.0.1:60678 | teampass | Query   |    2 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834245' |
| 105646 | teampass_user | 127.0.0.1:60932 | teampass | Query   |    8 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834239' |
| 105631 | teampass_user | 127.0.0.1:63608 | teampass | Query   |    7 | updating  | DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834240' |
| 105647 | teampass_user | 127.0.0.1:60942 | teampass | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'Sleep'        |
+--------+---------------+-----------------+----------+---------+------+-----------+------------------------------------------------------------------------------+
13 rows in set, 1 warning (0,00 sec)

Number of rows in the table :

mysql> SELECT COUNT(*) FROM teampass_background_tasks_logs;
+----------+
| COUNT(*) |
+----------+
|   551656 |
+----------+
1 row in set (0,06 sec)

MySQL Explain :

mysql> EXPLAIN DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834240';
+----+-------------+--------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                          | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | DELETE      | teampass_background_tasks_logs | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 521853 |   100.00 | Using where |
+----+-------------+--------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0,01 sec)

Official database :

mysql> SHOW CREATE TABLE teampass_background_tasks_logs;
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table
                                                                                                                                                                                                                         |
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teampass_background_tasks_logs | CREATE TABLE `teampass_background_tasks_logs` (
  `increment_id` int NOT NULL AUTO_INCREMENT,
  `created_at` varchar(20) NOT NULL,
  `job` varchar(50) NOT NULL,
  `status` varchar(10) NOT NULL,
  `updated_at` varchar(20) DEFAULT NULL,
  `finished_at` varchar(20) DEFAULT NULL,
  `treated_objects` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=551659 DEFAULT CHARSET=utf8mb3 |
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

To improve :

• Storage of timestamp must be in integer field.
• Add index to created_at (in where).

Altering table :

ALTER TABLE teampass_background_tasks_logs MODIFY created_at INT NOT NULL;
ALTER TABLE teampass_background_tasks_logs MODIFY updated_at INT DEFAULT NULL;
ALTER TABLE teampass_background_tasks_logs MODIFY finished_at INT DEFAULT NULL;
ALTER TABLE teampass_background_tasks_logs ADD INDEX idx_created_at (created_at);

After :

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'Sleep';
+----+---------------+-----------------+----------+---------+------+-----------+-----------------------------------------------------------------------+
| ID | USER          | HOST            | DB       | COMMAND | TIME | STATE     | INFO                                                                  |
+----+---------------+-----------------+----------+---------+------+-----------+-----------------------------------------------------------------------+
|  8 | teampass_user | 127.0.0.1:17904 | teampass | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> 'Sleep' |
+----+---------------+-----------------+----------+---------+------+-----------+-----------------------------------------------------------------------+
1 row in set, 1 warning (0,01 sec)

mysql> EXPLAIN DELETE FROM `teampass_background_tasks_logs` WHERE created_at < '1714834240';
+----+-------------+--------------------------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table                          | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------------------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | teampass_background_tasks_logs | NULL       | range | idx_created_at | idx_created_at | 4       | const |    1 |   100.00 | Using where |
+----+-------------+--------------------------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

mysql> SHOW CREATE TABLE teampass_background_tasks_logs;
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table
                                                                                                                                                                                                                                        |
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teampass_background_tasks_logs | CREATE TABLE `teampass_background_tasks_logs` (
  `increment_id` int NOT NULL AUTO_INCREMENT,
  `created_at` int NOT NULL,
  `job` varchar(50) NOT NULL,
  `status` varchar(10) NOT NULL,
  `updated_at` int DEFAULT NULL,
  `finished_at` int DEFAULT NULL,
  `treated_objects` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`increment_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=551659 DEFAULT CHARSET=utf8mb3 |
+--------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,04 sec)

Server configuration

Operating system: EL8

Web server: Apache

Database: mysql 8.0.37

PHP version: 8.3.7

Teampass version: master branch