RDS slow queries after few weeks

Question

Since I updated from MySQL 5.6 to 5.7 I started to have very slow queries in my Aurora RDS instance. In my local XAMPP the same queries took a few seconds but in RDS some of them were about 6 minutes. Trying to offer a fast solution for my customer I migrated the database from Aurora RDS to a normal MariaDB RDS instance (probably overkill but I needed to do something fast). After the migration query times were similar to local environment, acceptable times, but it's been three weeks since the migration and queries are very slow again, about 6 minutes. CPU rises to almost 50% and there is only one connection in DB, the one doing the slow query. I've checked and modified parameters in RDS, specially the optimizer_switch, but they had no visible effect in the query speed. I've also optimized tables with no improvement. I was thinking about a log table or similar which could be growing too much after three weeks and therefore slowing my queries but it's just an idea. At this point I'm a bit lost. Tables in these slow queries are between 63 a 200K+ rows.

The slowest query is this:

SELECT *
FROM bicycles AS Bicycle
LEFT JOIN loans AS Loan ON Loan.uuid = (
  SELECT 
  loans.uuid
  FROM loans
  WHERE loans.bicycle_uuid = Bicycle.uuid
  ORDER BY loans.date_created DESC
  LIMIT 1
)
WHERE Bicycle.status = 'SCRAPPED'
AND Bicycle.number <= 9900
ORDER BY Loan.date_created DESC, Bicycle.number DESC

961 rows in set (6 min 23.28 sec)

Here's the explain for the query:

+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
| id | select_type        | table   | partitions | type   | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                        |
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | Bicycle | NULL       | ALL    | number        | NULL         | NULL    | NULL | 2082 |     9.99 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | Loan    | NULL       | eq_ref | PRIMARY       | PRIMARY      | 16      | func |    1 |   100.00 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | loans   | NULL       | index  | bicycle_uuid  | date_created | 5       | NULL |  428 |    10.00 | Using where                                  |
+----+--------------------+---------+------------+--------+---------------+--------------+---------+------+------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

Indexes for table bicycles:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bicycles |          0 | PRIMARY  |            1 | uuid        | A         |        2082 |     NULL | NULL   |      | BTREE      |         |               |
| bicycles |          0 | number   |            1 | number      | A         |        2082 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

Indexes for table loans:

+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| loans |          0 | PRIMARY                   |            1 | uuid         | A         |      219864 |     NULL | NULL   |      | BTREE      |         |               |
| loans |          1 | anchor_uuid               |            1 | anchor_uuid  | A         |        1047 |     NULL | NULL   |      | BTREE      |         |               |
| loans |          1 | use_uuid                  |            1 | use_uuid     | A         |        4076 |     NULL | NULL   | YES  | BTREE      |         |               |
| loans |          1 | date_created              |            1 | date_created | A         |      213634 |     NULL | NULL   |      | BTREE      |         |               |
| loans |          1 | bicycle_uuid              |            1 | bicycle_uuid | A         |         428 |     NULL | NULL   |      | BTREE      |         |               |
| loans |          1 | date_created_bicycle_uuid |            1 | date_created | A         |      212288 |     NULL | NULL   |      | BTREE      |         |               |
| loans |          1 | date_created_bicycle_uuid |            2 | bicycle_uuid | A         |      218719 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

I'm using a db.r3.large instance, 2vCPUs, 15.25 GiB RAM, Not EBS Optimized. MySQL version is 5.7.34 with MariaDB. Storage is an IOPS provisioned SSD (io1), 100GiB assigned storage, 5000 provisiones IOPS.

As per user request I add the next tables information:

+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bicycles | CREATE TABLE `bicycles` (
  `uuid` binary(16) NOT NULL,
  `number` int(14) NOT NULL,
  `date_created` datetime NOT NULL,
  `status` varchar(20) COLLATE utf8_spanish_ci NOT NULL DEFAULT 'CIRCULATION',
  `is_electric` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)


+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| bicycles | InnoDB |      10 | Dynamic    | 2082 |             78 |      163840 |               0 |        81920 |         0 |           NULL | 2022-01-28 12:13:25 | 2022-01-28 12:13:25 | NULL       | utf8_spanish_ci |     NULL |                |         |                0 | N         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)


+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| loans | CREATE TABLE `loans` (
  `uuid` binary(16) NOT NULL,
  `use_uuid` binary(16) DEFAULT NULL,
  `bicycle_uuid` binary(16) NOT NULL,
  `anchor_uuid` binary(16) NOT NULL,
  `date_created` datetime NOT NULL,
  `type_access` enum('UNKNOWN','CARD','APP','SYSTEM','WORKER') COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `anchor_uuid` (`anchor_uuid`),
  KEY `use_uuid` (`use_uuid`),
  KEY `date_created` (`date_created`),
  KEY `bicycle_uuid` (`bicycle_uuid`),
  KEY `date_created_bicycle_uuid` (`date_created`,`bicycle_uuid`),
  CONSTRAINT `fk_loans_anchors` FOREIGN KEY (`anchor_uuid`) REFERENCES `anchors` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_loans_bicycles` FOREIGN KEY (`bicycle_uuid`) REFERENCES `bicycles` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_loans_users` FOREIGN KEY (`use_uuid`) REFERENCES `users` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)


+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name  | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| loans | InnoDB |      10 | Dynamic    | 216920 |             99 |    21561344 |               0 |     75284480 |   7340032 |           NULL | 2022-01-28 12:37:06 | 2022-01-28 12:37:22 | NULL       | utf8_spanish_ci |     NULL |                |         |                0 | N         |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)

This the EXPLAIN for the sentence after adding suggested INDEX:

+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type        | table      | partitions | type   | possible_keys                     | key                        | key_len | ref                           | rows | filtered | Extra                                                     |
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
|  1 | PRIMARY            | Bicycle    | NULL       | range  | number,bicycles_ndx_status_number | bicycles_ndx_status_number | 66      | NULL                          |  961 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | Loan       | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | func                          |    1 |   100.00 | Using where                                               |
|  1 | PRIMARY            | LoanAnchor | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | bicicas.Loan.anchor_uuid      |    1 |   100.00 | NULL                                                      |
|  1 | PRIMARY            | LoanBench  | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | bicicas.LoanAnchor.bench_uuid |    1 |   100.00 | NULL                                                      |
|  1 | PRIMARY            | User       | NULL       | eq_ref | PRIMARY                           | PRIMARY                    | 16      | func                          |    1 |   100.00 | Using where                                               |
|  3 | DEPENDENT SUBQUERY | users      | NULL       | ref    | uuid                              | uuid                       | 16      | bicicas.Loan.use_uuid         |    2 |   100.00 | Using index condition; Using filesort                     |
|  2 | DEPENDENT SUBQUERY | loans      | NULL       | ref    | bicycle_uuid                      | bicycle_uuid               | 16      | bicicas.Bicycle.uuid          |  452 |   100.00 | Using index condition; Using filesort                     |
+----+--------------------+------------+------------+--------+-----------------------------------+----------------------------+---------+-------------------------------+------+----------+-----------------------------------------------------------+
7 rows in set, 3 warnings (0.00 sec)

Answer 1

Suggestion/Observations to consider to reduce time required for query completion.

CREATE INDEX bicycles_ndx_status_number ON bicycles (status, number);  

Test your query to see if same results are provided but faster.

EXPLAIN SELECT (your query) and observe ROWS column value.  Posted EXPLAIN had ROWS at 2,082.  The index should reduce the ROWS number significantly, saving time.

Observation, AND Bicycle.number <= 9900 could be AND Bicycle.number between 0 and 9899 and could reduce time required.

Observation 2 singular vs plural table name qualifiers. Is confusing to me when both are used but may be best for you.

Observation 3 first line of defense when any query is slow ANALYZE TABLE table_name;

EACH table to ensure indexes are CURRENT (they could have somehow become corrupted). This should be first attempt at correcting any SLOW query.

Would not help you much because you NEED the additional BICYCLES multi-column index for high performance.