MySQL explain plan doesn't show key lookup

Question

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

Answer 1

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