MySQL never completes query with a subquery, but completes when queries are run separately

This is the query that never finishes:

SELECT modelId, `timestamp` FROM thread_view WHERE id IN 
  (SELECT max(id) FROM thread_view
   WHERE viewerId = 1 AND modelType = 'LEASE' AND modelId IN 
   (15,615,618,660) GROUP BY modelId);

When I run just the subquery, it completes in 12ms and returns two ids. When I hardcode those 2 ids in place of the subquery, the outerquery returns two rows and completes in 0.3ms.

As you can see, both parts of the query run incredibly quickly and it’s not like we’re dealing with a lot of data here. When I run the EXPLAIN:

The explain didn’t reveal any problems to me. Is this just a bug with MySQL?

EDIT: mysql –version gives: mysql Ver 8.0.25 for macos11.3 on x86_64 (Homebrew)

EDIT #2: As requested, here is the create table statement:

CREATE TABLE `thread_view` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` bigint DEFAULT NULL,
  `viewerId` bigint DEFAULT NULL,
  `modelType` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `modelId` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `viewerId_modelType_modelId` (`viewerId`,`modelType`,`modelId`)
) ENGINE=InnoDB AUTO_INCREMENT=50582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Answer

If the goal is really to grab the greatest timestamp per model (also noted by @Shaharyar):

SELECT modelId
     , MAX(`timestamp`)
  FROM thread_view
 WHERE modelId IN (15,615,618,660)
   AND viewerId = 1
   AND modelType = 'LEASE'
 GROUP BY modelId
;

Advertisement

Answer