Select latest child for each parent optimization

Question

I have 2 tables, a keyword table and a keyword position table

keyword structure

CREATE TABLE `keywords`
(
    `id`      bigint(20) UNSIGNED                     NOT NULL AUTO_INCREMENT,
    `keyword` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

keyword_positions structure

CREATE TABLE `keyword_positions`
(
    `id`         bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `keyword_id` bigint(20) UNSIGNED NOT NULL,
    `position`   int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

This is how I preform my select

SELECT `keywords`.*,
       kp.position AS kp_position
FROM `keywords`
         LEFT JOIN `keyword_positions` AS `kp`
                   ON `kp`.`keyword_id` = `keywords`.`id`
                       AND `kp`.`id` =
                           (SELECT MAX(ikp.id)
                            FROM keyword_positions AS ikp
                            WHERE keywords.id = ikp.keyword_id)

Can this query be optimized further? it runs for about 800 MS

both ids are unique and auto incrementing, updating my question, thank you — phper, Mar 22 '21 at 08:18

score 1 · Answer 1 · answered Mar 22 '21 at 23:08

Ok, I get much better performance when I don't use MAX, This is my new query

SELECT `keywords`.*,
       kp.position AS kp_position
FROM `keywords`
         left join `keyword_positions` as `kp`
                   on `kp`.`keyword_id` = `keywords`.`id`
                       and `kp`.`id` = (SELECT ikp.id
                                        FROM keyword_positions AS ikp
                                        WHERE keywords.id = ikp.keyword_id
                                        ORDER BY ikp.id DESC
                                        LIMIT 1)

runs for 30-60 MS

score 0 · Answer 2 · answered Mar 23 '21 at 05:20

0

The innermost subquery will probably run a lot faster if ikp had

INDEX(keyword_id, id)

With that, the Optimizer can do MAX() much more efficiently.

answered Mar 23 '21 at 05:20

Rick James

135,179
13
127
222

Select latest child for each parent optimization

2 Answers2