3

I've got two very simple tables

CREATE TABLE `track` (
  `id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `number` int NOT NULL DEFAULT '-1',
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `updated` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  PRIMARY KEY (`id`),
  KEY `updated` (`updated`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `trackindex` (
  `track_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `track_updated` datetime NOT NULL,
  PRIMARY KEY (`track_id`),
  KEY `track_updated` (`track_updated`),
  KEY `track_id-track_updated` (`track_id`,`track_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

The track table has about 2.1 million rows. The following query takes about 1 minute to complete :

SELECT 
    trackindex.track_id AS track_id1_26_0_,
    trackindex.track_updated AS track_up2_26_0_
FROM
    trackindex
        LEFT OUTER JOIN
    track ON trackindex.track_id = track.id
WHERE
    track.id IS NULL
        OR trackindex.track_updated <> track.updated
LIMIT 50

enter image description here

What can I do to make this query run faster?

Side note : the query exectures on an AWS RDS db.m6g.xlarge instance which can do 1500 IOPS.

3 Answers 3

1

Add the following composite index :

alter table track add index `id_updated`(`id`,`updated`);

It is known that OR it's a performance killer. You could rewrite the query using UNION ALL .

SELECT ti.track_id AS track_id1_26_0_,
       ti.track_updated AS track_up2_26_0_
FROM trackindex ti 
LEFT OUTER JOIN track  t ON ti.track_id = t.id
WHERE t.id IS NULL

UNION ALL

SELECT ti.track_id AS track_id1_26_0_,
       ti.track_updated AS track_up2_26_0_
FROM trackindex ti 
LEFT OUTER JOIN track  t ON ti.track_id = t.id
WHERE ti.track_updated <> t.updated 
LIMIT 50 ;
0

Your query essentially states that you want all rows which either have no matching track row, or have a track row which has a different updated time.

So you can try the following equivalent query

SELECT 
    ti.track_id AS track_id1_26_0_,
    ti.track_updated AS track_up2_26_0_
FROM
    trackindex ti
WHERE NOT EXISTS (SELECT 1
    FROM
        track t
    WHERE
        ti.track_id = t.id
        AND ti.track_updated = t.updated
)
LIMIT 50;
0

See if this gives what you want:

SELECT  ti.track_id AS track_id1_26_0_,
        ti.track_updated AS track_up2_26_0_
    FROM  trackindex AS ti
    LEFT OUTER JOIN  track  ON ti.track_id = track.id
      AND  ti.track_updated = track.updated
    WHERE  track.id IS NULL
    LIMIT  50 

It may not be correct if the mapping is not what it expects. Is it 1:many, or what?

In any case, this is redundant, hence unnecessary:

KEY `track_id-track_updated` (`track_id`,`track_updated`)

You have no ORDER BY, so the LIMIT is free to give you any 50. If you add an ORDER BY, we would need to analyze things again; the indexing may change.

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.