I have the following query:
SELECT
`Job`.`id`,
`Job`.`serviceId`,
`Job`.`externalId`,
`Job`.`createdAt`,
`Job`.`updatedAt`,
`logs`.`id` AS `logs.id`,
`logs`.`statusId` AS `logs.statusId`,
`logs`.`createdAt` AS `logs.createdAt`
FROM `Jobs` AS `Job`
LEFT OUTER JOIN (
SELECT id, jobId, statusId, createdAt, updatedAt
FROM JobLogs
WHERE id IN (
SELECT MAX(id)
FROM JobLogs
GROUP BY jobId
)
) AS `logs` ON `Job`.`id` = `logs`.`jobId`
ORDER BY `Job`.`id` DESC;
It returns all Jobs with only the latest JobLog. It works fine and testing it in e.g. Sequel Pro connecting to the DB running in a container returns all 1000 jobs I previously added in +/-10ms.
Because my API is written in node and I'm using sequelize/mysql2 under the hood, I have just copied the above query into a sequelize.query() snippet (and for testing purposes a bare-metal mysql2-query-snippet w/ the same result) -> the query takes 35sec mysql process stays at 100% CPU.
Why is the query so much slower when using node/mysql2 compared to e.g. Sequel Pro? What am I missing here?
It's not the LIMIT 1000 the client adds because there are only 1000 entries in the table ...
edit
$ EXPLAIN <SQL> returns the following:
+----+-------------+---------+------------+-------+---------------+---------+---------+-----------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-----------+------+----------+--------------------------+
| 1 | PRIMARY | Job | NULL | index | NULL | PRIMARY | 4 | NULL | 1000 | 100.00 | NULL |
| 1 | PRIMARY | JobLogs | NULL | ref | jobId | jobId | 4 | db.Job.id | 13 | 100.00 | Using where |
| 3 | SUBQUERY | JobLogs | NULL | range | jobId | jobId | 4 | NULL | 1000 | 100.00 | Using index for group-by |
+----+-------------+---------+------------+-------+---------------+---------+---------+-----------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
And I'm using the mysql:5.7.20 docker container without any extra configuration that I know of ...
edit 2
It doesn't look like it is a cache thing. Dropping and recreating the database and then inserting new (random) values and then doing the first query with Sequel Pro leads to a ~30ms response.
