MySQL – EXPLAIN Plan Doesn’t Show Key Lookup

execution-planMySQLquery-performance

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

Best Answer

  • 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);
Related Question