1

I have a query that's really slow when I select a lot of columns but greatly speeds up when I only select the columns present in the index the query is using. This makes me believe that the query is slowing down because of a key lookup but a key lookup is not shown in the query execution plan.

I know this exists in SQL Server but is it possible to show key lookups in the query execution plan in MySQL 5.7?

The slow query:

select c.*
from composite c
inner join creative_library cl on c.creative_library_id=cl.creative_library_id
inner join ad_format a on cl.ad_format_id=a.ad_format_id
where c.deleted=0 and cl.deleted=0 order by c.created_on desc limit 25;
select_type table type key key_len ref rows filtered Extra
SIMPLE creative index creative_library__creative_library_id__deleted__ad_format 9 null 748 10 Using where; Using index; Using temporary; Using filesort
SIMPLE ad_format eq_ref PRIMARY 4 creative_library.ad_format_id 1 100 Using index
SIMPLE composite ref composite__creative_library_id__deleted__created_on_desc__id 5 creative_library.creative_library_id, const 2179 100 null

The faster query:

select c.created_on, c.creative_library_id, c.deleted
from composite c
inner join creative_library cl on c.creative_library_id=cl.creative_library_id
inner join ad_format a on cl.ad_format_id=a.ad_format_id
where c.deleted=0 and cl.deleted=0 order by c.created_on desc limit 25

The faster query has the same execution plan.

I have an index on the composite table that covers all of the fields in the faster query.

CREATE TABLE `composite` (
    `placement_cue_id` int(10) unsigned DEFAULT NULL,
    `creative_library_id` int(10) unsigned NOT NULL,
    `deleted` tinyint(1) NOT NULL,
    `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `entity_key` varchar(64) NOT NULL DEFAULT '',
    `publisher_id` int(10) unsigned DEFAULT NULL,
    `composite_job_id` varchar(36) DEFAULT NULL,
    `id` int(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`),
     UNIQUE KEY `entity_key` (`entity_key`),
     UNIQUE KEY `placement_cue_id` (`placement_cue_id`,`creative_library_id`),
     KEY `publisher_id` (`publisher_id`),
     KEY `composite_job_id` (`composite_job_id`),
     KEY `composite__creative_library_id__deleted__created_on_desc__id` (`creative_library_id`,`deleted`,`created_on`,`id`),
    CONSTRAINT `FK__COMPOSITE_COMPOSITE_JOB` FOREIGN KEY (`composite_job_id`) REFERENCES `composite_job` (`job_id`),
    CONSTRAINT `FK__COMPOSITE__CREATIVE_LIBRARY` FOREIGN KEY (`creative_library_id`) REFERENCES `creative_library` (`creative_library_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1631561 DEFAULT CHARSET=latin1


CREATE TABLE `creative_library` (
    `creative_library_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `ad_format_id` int(10) unsigned NOT NULL,
    `deleted` tinyint(1) NOT NULL,
    `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `title` varchar(254) DEFAULT NULL,
    PRIMARY KEY (`creative_library_id`),
    UNIQUE KEY `unique_title` (`title`),
    KEY `creative_library__creative_library_id__deleted__ad_format` (`creative_library_id`,`deleted`,`ad_format_id`),
    KEY `creative_library__ad_format__deleted__creative_library_id` (`ad_format_id`,`deleted`,`creative_library_id`),
    CONSTRAINT `FK__CREATIVE_LIBRARY__AD_FORMAT` FOREIGN KEY (`ad_format_id`) REFERENCES `ad_format` (`ad_format_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=751 DEFAULT CHARSET=latin1
3
  • I'm using MySQL 5.7, EXPLAIN ANALYZE doesn't work
    – Archmede
    May 3, 2023 at 17:42
  • Please provide SHOW CREATE TABLE and EXPLAIN SELECT ...
    – Rick James
    May 3, 2023 at 19:10
  • @RickJames I've added table definitions and execution plans
    – Archmede
    May 4, 2023 at 15:37

1 Answer 1

2
  • Why JOIN to ad_format, but then not use it? This may be a slowdown in both queries.

  • INDEX(deleted, created_at) (in that order) may help.

  • It sounds like there are TEXT or BLOB columns when fetching c.*. This, together with the inability to make good use of indexes.

  • If the two deleted columns track each other, get rid of the JOIN cl.

  • Forcing a lazy load be needed:

    select  c.*
      FROM  
      (
          SELECT  c.id
              from  composite AS c
              inner join  creative_library cl  ON c.creative_library_id=cl.creative_library_id
              inner join  ad_format a  ON cl.ad_format_id=a.ad_format_id
              where  c.deleted=0
                and  cl.deleted=0
              order by  c.created_on desc
              limit  25 
      ) AS x
      JOIN  composite AS c2 USING(id);
    
2
  • This answer has a good solution to fix my problem, and it proves that the issue was caused by a key lookup, but do you know why the key-lookup isn't shown in the execution plan?
    – Archmede
    May 4, 2023 at 15:42
  • 1
    @Archmede - Short answer: EXPLAIN is lame. More: EXPLAIN FORMAT=JSON, EXPLAIN ANALYZE, "Optimizer trace".
    – Rick James
    May 4, 2023 at 18:17

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.