1

I recently added a JOIN to a query look up one of three names based on id. Normally this has little to no effect on performance but for some reason in this case when I SELECT the column in question the entire query becomes very slow and EXPLAIN reveals that the index is not used.

Here is a DB Fiddle demonstrating: https://www.db-fiddle.com/f/3WhrpEDLLn8ifLwocUXPHe/0

And here is schema and some sample data:

CREATE TABLE `session` (
  `session_id` int NOT NULL AUTO_INCREMENT,
  `facility_id` int NOT NULL,
  PRIMARY KEY (`session_id`),
  KEY `facility_id` (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `facility` (
  `facility_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`facility_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `facility` (`facility_id`, `name`)
VALUES
  (1, 'A'),
  (2, 'B'),
  (3, 'C');

INSERT INTO `session` (`session_id`, `facility_id`)
VALUES
  (47718, 1),
  (47716, 2),
  (47712, 2),
  (47710, 2),
  (47703, 2),
  (47701, 2),
  (47700, 2),
  (47699, 2),
  (47698, 2),
  (47697, 2),
  (47696, 2),
  (47695, 2),
  (47694, 1),
  (47693, 2),
  (47692, 2),
  (47691, 2),
  (47690, 2),
  (47689, 1),
  (47688, 2),
  (47687, 2),
  (47686, 1),
  (47685, 2),
  (47684, 2),
  (47683, 2),
  (47682, 2),
  (47681, 2),
  (47680, 2),
  (47679, 2),
  (47678, 2);

Running the following two queries reveals that by simply selecting the facility name the index is not used:

EXPLAIN 
SELECT    
  s.session_id,
  f.name AS facility
FROM `session` s
  JOIN facility f USING (facility_id);
  
EXPLAIN 
SELECT    
  s.session_id
FROM `session` s
  JOIN facility f USING (facility_id);

In my real world scenario, the session table has approx 50,000 rows and the query involves 6 other joins. The performance impact of selecting the facility name makes a query which normally takes approx 39ms go as long as 8 full seconds. Oddly, if I omit selecting the facility name the query performance is fine, even with the join intact.

The server is running MySQL 8.0.36.

UPDATE: Wild guess time. Running format=json in my explains shows me that the optimizer is doing "access_type": "ALL" in the slowest case vs "access_type": "index" or "access_type": "eq_ref" where the query performs "normally". I found that by changing to a LEFT JOIN and moving it toward the end I can force the optimizer to use eq_ref and get the same results approx 200x faster. This seems more like a workaround to a bug than an actual solution though.

3

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.