CODEWITHSUNDEEP
mysqlgreatest-n-per-groupmariadb-10.5
phper
phper

Reputation: 321

Select latest child for each parent optimization

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

Upvotes: 0

Views: 52

Answers (2)

Rick James
Rick James

Reputation: 142453

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.

Upvotes: 0

phper
phper

Reputation: 321

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

Upvotes: 1

Related Questions