Using LIMIT makes query much slower - optimiser moves ORDER BY

Question

I have the following query, which runs slowly:

SELECT l.track, r.rating
FROM library l LEFT JOIN rating r ON r.type='song' AND r.id=26452 AND r.value=CONCAT(l.name, ':', l.path)
WHERE l.id=26452 
ORDER BY title ASC
LIMIT 20 OFFSET 0

EXPLAIN output:

+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref         | rows  | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+
|  1 | SIMPLE      | l     | NULL       | index | id,user_id    | title   | 2050    | NULL        | 14379 |     0.07 | Using where |
|  1 | SIMPLE      | r     | NULL       | ref   | PRIMARY       | PRIMARY | 66      | const,const |     2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+-------+----------+-------------+

With EXPLAIN ANALYZE that gives:

-> Limit: 20 row(s)  (cost=47313.37 rows=20) (actual time=60.414..1700.587 rows=20 loops=1)
    -> Nested loop left join  (cost=47313.37 rows=21) (actual time=60.411..1700.573 rows=20 loops=1)
        -> Filter: (l.id = 26452)  (cost=10.45 rows=11) (actual time=60.370..1699.964 rows=20 loops=1)
            -> Index scan on l using title  (cost=10.45 rows=14379) (actual time=0.141..1688.749 rows=120049 loops=1)
        -> Filter: (r.`value` = concat(l.`name`,':',l.`path`))  (cost=1.70 rows=2) (actual time=0.028..0.028 rows=0 loops=20)
            -> Index lookup on r using PRIMARY (id=26452, type='song')  (cost=1.70 rows=2) (actual time=0.016..0.018 rows=2 loops=20)

The thing that surprises me here is that the ORDER BY seems to be performed before the filtering and where criteria on l.id. As a result the actual rows processed is 120049 and it ends up performing an index scan. Note the rows estimate is actually quite accurate:

mysql> select count(*) from library where id=26452;
+----------+
| count(*) |
+----------+
|    15053 |
+----------+

If I remove the LIMIT and OFFSET statements, the query is much faster:

-> Nested loop left join  (cost=80466.96 rows=55648) (actual time=128.144..193.327 rows=15053 loops=1)
    -> Sort: l.title  (cost=30383.76 rows=27824) (actual time=128.058..135.680 rows=15053 loops=1)
        -> Index lookup on l using user_id (id=26452)  (actual time=0.145..48.220 rows=15053 loops=1)
    -> Filter: (r.`value` = concat(l.`name`,':',l.`path`))  (cost=1.60 rows=2) (actual time=0.004..0.004 rows=0 loops=15053)
        -> Index lookup on r using PRIMARY (id=26452, type='song')  (cost=1.60 rows=2) (actual time=0.002..0.002 rows=2 loops=15053)

Here, the sort runs much faster because it runs on far fewer rows.

Table definitions

CREATE TABLE `library` (
  `id` int NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `path` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `album` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `art` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `artist` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `band` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `composer` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `duration` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `genre` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `title` varchar(512) COLLATE utf8mb4_general_ci NOT NULL,
  `disc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `track` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
  `year` varchar(200) COLLATE utf8mb4_general_ci NOT NULL,
  `format` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `bitrate` double NOT NULL,
  `filesize` int NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `edited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `db_id` int NOT NULL,
  `tag` varchar(64) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `rating` (
  `id` int NOT NULL,
  `type` varchar(20) NOT NULL,
  `value` varchar(150) NOT NULL,
  `rating` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `library`
  ADD PRIMARY KEY (`db_id`),
  ADD KEY `album` (`album`(191)),
  ADD KEY `artist` (`artist`(191)),
  ADD KEY `id` (`id`,`name`,`path`(191)),
  ADD KEY `genre` (`genre`(191)),
  ADD KEY `edited` (`edited`),
  ADD KEY `created` (`created`),
  ADD KEY `track` (`track`),
  ADD KEY `title` (`title`),
  ADD KEY `disc` (`disc`),
  ADD KEY `band` (`band`),
  ADD KEY `art` (`art`),
  ADD KEY `user_id` (`id`);
ALTER TABLE `library` ADD FULLTEXT KEY `all` (`album`,`artist`,`band`,`composer`,`genre`,`title`,`year`);

ALTER TABLE `rating`
  ADD PRIMARY KEY (`id`,`type`,`value`),
  ADD KEY `rating` (`rating`);

Sample data

INSERT INTO `library` VALUES (26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/3-Tessellate.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','182.66666666667','Folk/Rock','Tessellate','','03','2012','flac',1101107.8248175,25141962,'2023-08-25 09:10:21','2023-08-25 09:10:21',88464632,'02588db1b1c7fb3f5847da6cd334338f'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/2-(Interlude_1).flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','72.08','Folk/Rock','(Interlude 1)','','02','2012','flac',1129123.0854606,10173399,'2023-08-25 09:10:21','2023-08-25 09:10:21',88464633,'5630f1fbae265a751a9e7b5512f1f97d'),(26452,'GDrive - scratch','A/test-libraries/scratch/01 - Tangled Up In Blue.flac','Blood On The Tracks','','Bob Dylan','','','340.52','Folk & Rock','Tangled Up In Blue','','01','1975','flac',954125.50217315,40617131,'2023-08-25 09:10:23','2023-08-25 09:10:23',88464634,'bdc2868fdb8dfee2952adcb9120f450a'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/1-Intro.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','157.81333333333','Folk/Rock','Intro','','01','2012','flac',1060156.809733,20913360,'2023-08-25 09:10:23','2023-08-25 09:10:23',88464635,'37cea636f5e7efb02f9978f98e993ba9'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/4-Breezeblocks.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','227.08','Folk/Rock','Breezeblocks','','04','2012','flac',1042233.4683812,29583797,'2023-08-25 09:10:24','2023-08-25 09:10:24',88464636,'25102160c22f11c8c9a5fd359caa224c'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/5-(Interlude_2).flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','77.4','Folk/Rock','(Interlude 2)','','05','2012','flac',1110776.2273902,10746760,'2023-08-25 09:10:25','2023-08-25 09:10:25',88464637,'7564a31860d75727902dbcc5e6e0bf1f'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/6-Something_Good.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','218.24','Folk/Rock','Something Good','','06','2012','flac',1031448.9369501,28137927,'2023-08-25 09:10:26','2023-08-25 09:10:26',88464638,'3cc973bc2422ee271a836be092941b7d'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/8-Matilda.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','228.70666666667','Folk/Rock','Matilda','','08','2012','flac',967785.93831983,27667387,'2023-08-25 09:10:27','2023-08-25 09:10:27',88464639,'03ee261b3f42c8e449e886b471321bb2'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/9-Ms.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','238.97333333333','Folk/Rock','Ms','','09','2012','flac',945418.52368465,28241227,'2023-08-25 09:10:28','2023-08-25 09:10:28',88464640,'3fba955cfe1b9f0ea804d3d5a4f5d524'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/10-Fitzpleasure.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','219.4','Folk/Rock','Fitzpleasure','','10','2012','flac',999773.7101185,27418794,'2023-08-25 09:10:29','2023-08-25 09:10:29',88464641,'2f8f71f4aca1017ba9fc62e1d07daf21'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/11-(Interlude_3).flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','53.6','Folk/Rock','(Interlude 3)','','11','2012','flac',1183189.5522388,7927370,'2023-08-25 09:10:30','2023-08-25 09:10:30',88464642,'4cd364598a5ed7616ac3cb093cc18bfe'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/12-Bloodflood.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','249.42666666667','Folk/Rock','Bloodflood','','12','2012','flac',859819.08376544,26807726,'2023-08-25 09:10:31','2023-08-25 09:10:31',88464643,'1028e8e524bfe6664cc6a021bf77b45f'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/01-THE_VIEW_FROM_THE_AFTERNOON.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','218.13333333333','RAP','the view from the afternoon','','001','2006','ogg',117923.69193154,3289466,'2023-08-25 09:10:32','2023-08-25 09:10:32',88464644,'d9dfbf199ddcd964aed8ee66592780cb'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/02-I_BET_YOU_LOOK_GOOD_ON_THE_DANCEFLOOR.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','173.68','RAP','i bet you look good on the dancefloor','','002','2006','ogg',117601.10548135,2625986,'2023-08-25 09:10:33','2023-08-25 09:10:33',88464645,'41ff5c5b8b03b5504dde219b3b39e569'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/13-Taro_&_Handmade.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','761.62666666667','Folk/Rock','Taro & Handmade','','13','2012','flac',480492.07310668,45744447,'2023-08-25 09:10:33','2023-08-25 09:10:33',88464646,'c63a286070fb0ee400a03881ee169392'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/03-FAKE_TALES_OF_SAN_FRANCISCO.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','177.94666666667','RAP','fake tales of san francisco','','003','2006','ogg',115716.24456766,2646270,'2023-08-25 09:10:33','2023-08-25 09:10:33',88464647,'2d203685f885d5df22e67387e74b3260'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/04-DANCING_SHOES.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','141.13333333333','RAP','dancing shoes','','004','2006','ogg',119033.25460557,2173242,'2023-08-25 09:10:34','2023-08-25 09:10:34',88464648,'9f39fa0df2ea8bc4ee98841d089fe644'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/05-YOU_PROBABLY_COULDN’T_SEE_FOR_THE_LIGHTS_BUT_YOU_WERE_STARING_STRAIGHT_AT_ME.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','130.81333333333','RAP','you probably couldn’t see for the lights but you were staring straight at me','[no title]','005','2006','ogg',115819.08062379,1966522,'2023-08-25 09:10:34','2023-08-25 09:10:34',88464649,'4b34d0b4433d52533678a10faa426291'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/06-STILL_TAKE_YOU_HOME.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','173.66666666667','RAP','still take you home','[no title]','006','2006','ogg',119280.41458733,2662054,'2023-08-25 09:10:35','2023-08-25 09:10:35',88464650,'94fc01b32937c282561a4de953873c7b'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/07-RIOT_VAN.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','134.93333333333','RAP','riot van','[no title]','007','2006','ogg',106505.39525692,1869000,'2023-08-25 09:10:35','2023-08-25 09:10:35',88464651,'fb3fd8d7c35f683348d57ddf5ec58cc7'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/08-RED_LIGHT_INDICATES_DOORS_ARE_SECURED.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','143.62666666667','RAP','red light indicates doors are secured','[no title]','008','2006','ogg',119721.61158559,2220796,'2023-08-25 09:10:36','2023-08-25 09:10:36',88464652,'3f5623baa6cc34449df734e4f7691ebd'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/09-MARDY_BUM.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','175.44','RAP','mardy bum','[no title]','009','2006','ogg',124005.38075695,2791511,'2023-08-25 09:10:36','2023-08-25 09:10:36',88464653,'a31ea1a457d9c05b0fd9990afb3f9e24'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/10-PERHAPS_VAMPIRES_IS_A_BIT_STRONG_BUT….ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','268.62666666667','RAP','perhaps vampires is a bit strong but…','[no title]','010','2006','ogg',113311.19273341,3876683,'2023-08-25 09:10:37','2023-08-25 09:10:37',88464654,'54818ba76bb68ad59597a11a50cd2178'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/11-WHEN_THE_SUN_GOES_DOWN.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','200.01333333333','RAP','when the sun goes down','[no title]','011','2006','ogg',112391.70721952,2881341,'2023-08-25 09:10:37','2023-08-25 09:10:37',88464655,'399692b83aa1ce08df885bf84c65391d'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/12-FROM_THE_RITZ_TO_THE_RUBBLE.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','193.42666666667','RAP','from the ritz to the rubble','[no title]','012','2006','ogg',113497.44261391,2816743,'2023-08-25 09:10:38','2023-08-25 09:10:38',88464656,'9b5e69c3e0b309ae648b1fe2785036ab'),(26452,'GDrive - scratch','A/test-libraries/scratch/Arctic_Monkeys-Whatever_People_Say_I_Am,_That’s_What_I’m_Not/13-A_CERTAIN_ROMANCE.ogg','Whatever People Say I Am, That’s What I’m Not','e0d2f8840d67eddb034f4f61f5e3b420','arctic monkeys','Arctic Monkeys','The Composer','331.2','RAP','a certain romance','[no title]','013','2006','ogg',121199.34782609,5089076,'2023-08-25 09:10:38','2023-08-25 09:10:38',88464657,'d383aa56bbe32ace2c6ea02a79d7e184'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/01-It_Could_Have_Been_A_Brilliant_Career','The Boy with the Arab Strap','','Belle and Sebastian','','','143.0986875','Jazz','It Could Have Been A Brilliant','','1','1998','mp3',128000,2290125,'2023-08-25 09:10:38','2023-08-25 09:10:38',88464658,'689a04ae39d6e2289d18a315ff07ab4c'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/02-Sleep_The_Clock_Around','The Boy with the Arab Strap','','Belle and Sebastian','','','298.18775','Jazz','Sleep The Clock Around','','2','1998','mp3',128000,4771549,'2023-08-25 09:10:39','2023-08-25 09:10:39',88464659,'eeb9ebfb2967d14e8db605e493606da5'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/03-Is_It_Wicked_Not_To_Care','The Boy with the Arab Strap','','Belle and Sebastian','','','202.52725','','Is It Wicked Not To Care?','','3','1998','mp3',128000,3240982,'2023-08-25 09:10:39','2023-08-25 09:10:39',88464660,'0b6ba9b875f64ae04919e4e9c9b56080'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/04-Ease_Your_Feet_In_The_Sea','The Boy with the Arab Strap','','Belle and Sebastian','','','215.7190625','Jazz','Ease Your Feet In The Sea','','4','1998','mp3',128000,3452051,'2023-08-25 09:10:40','2023-08-25 09:10:40',88464661,'e6002e2582996ac9959230ec0ef50f51'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/05-A_Summer_Wasting','The Boy with the Arab Strap','','Belle and Sebastian','','','126.746','Jazz','A Summer Wasting','','5','1998','mp3',128000,2028482,'2023-08-25 09:10:41','2023-08-25 09:10:41',88464662,'c88f62d3568c5a4170734301defcc7f5'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/07-A_Space_Boy_Dream','The Boy with the Arab Strap','','Belle and Sebastian','','','181.83825','Jazz','A Space Boy Dream','','7','1998','mp3',128000,2909958,'2023-08-25 09:10:41','2023-08-25 09:10:41',88464663,'cf777384e9092879c11cfcd4e217e235'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/06-Seymour_Stein','The Boy with the Arab Strap','','Belle and Sebastian','','','282.88','','Seymour Stein','','6','1998','mp3',128000,4526625,'2023-08-25 09:10:41','2023-08-25 09:10:41',88464664,'f7f45234a56428c64f70c4ce375b1fe3'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/08-Dirty_Dream_Number_Two','The Boy with the Arab Strap','','Belle and Sebastian','','','254.3541875','Jazz','Dirty Dream Number Two','','8','1998','mp3',128000,4070213,'2023-08-25 09:10:42','2023-08-25 09:10:42',88464665,'be127ad53e37d65ceaebd003e567e518'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/09-The_Boy_With_The_Arab_Strap','The Boy with the Arab Strap','','Belle and Sebastian','','','314.1224375','Jazz','The Boy With The Arab Strap','','9','1998','mp3',128000,5026504,'2023-08-25 09:10:42','2023-08-25 09:10:42',88464666,'43794991b64e93dc21f1db0a59bd1a37'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/10-Chickfactor','The Boy with the Arab Strap','','Belle and Sebastian','','','212.0619375','Jazz','Chickfactor','','10','1998','mp3',128000,3393537,'2023-08-25 09:10:42','2023-08-25 09:10:42',88464667,'d87b24c3b1bd335d4fd8ac545848b8fc'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/11-Simple_Things','The Boy with the Arab Strap','','Belle and Sebastian','','','106.1093125','Jazz','Simple Things','','11','1998','mp3',128000,1698295,'2023-08-25 09:10:43','2023-08-25 09:10:43',88464668,'187e4798c715b05d15a98c5550d0e0e8'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian/The_Boy_with_the_Arab_Strap/12-The_Rollercoaster_Ride','The Boy with the Arab Strap','','Belle and Sebastian','','','396.591','Jazz','The Rollercoaster Ride','','12','1998','mp3',128000,6346001,'2023-08-25 09:10:44','2023-08-25 09:10:44',88464669,'ccaab1e16b3e842711ec5953a2c0e4a0'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/01-THE_STARS_OF_TRACK_AND_FIELD.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','240.05725','Metal','the stars of track and field','','','1999','mp3',128000,3841044,'2023-08-25 09:10:45','2023-08-25 09:10:45',88464670,'f3555f8de94ab1bc3fee624e788f9b47'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/02-SEEING_OTHER_PEOPLE.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','179.58375','Metal','seeing other people','','','1999','mp3',128000,2873468,'2023-08-25 09:10:46','2023-08-25 09:10:46',88464671,'eb0723f8989ad7be216bda053f1cfdab'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/03-ME_AND_THE_MAJOR.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','183.42375','Metal','me and the major','','','1999','mp3',128000,2934908,'2023-08-25 09:10:48','2023-08-25 09:10:48',88464672,'7e786955514e383cd8d5f7eb46fa7cfc'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/04-LIKE_DYLAN_IN_THE_MOVIES.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','206.256','Metal','like dylan in the movies','','','1999','mp3',128000,3300224,'2023-08-25 09:10:48','2023-08-25 09:10:48',88464673,'046d15f752789148bde9cc187f5b7abd'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/05-THE_FOX_IN_THE_SNOW.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','202.6305625','Metal','the fox in the snow','','','1999','mp3',128000,3242217,'2023-08-25 09:10:49','2023-08-25 09:10:49',88464674,'1f43bbf907f7f4d4e48602a759a3a129'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/06-GET_ME_AWAY_FROM_HERE,_I’M_DYING.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','156.9403125','Metal','Get Me Away from Here, I\'m Dying','','','1999','mp3',128000,2511239,'2023-08-25 09:10:50','2023-08-25 09:10:50',88464675,'34a8a674cd8b5c12e838b36fc7fd8d13'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/07-IF_YOU\'RE_FEELING_SINISTER.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','272.6841875','Metal','if you\'re feeling sinister','','','1999','mp3',128000,4363075,'2023-08-25 09:10:51','2023-08-25 09:10:51',88464676,'09bf29295382c69d24c893a6279d50a2'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/08-MAYFLY.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','173.6539375','Metal','mayfly','','','1999','mp3',128000,2778591,'2023-08-25 09:10:51','2023-08-25 09:10:51',88464677,'a9941169b98e2834edc4335381473e0f'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/01-The_State_I_Am_In.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','298.0049','POP','the state i am in','','001','2008','mp3',160000,6312809,'2023-08-25 09:10:53','2023-08-25 09:10:53',88464678,'7b270e51c4c36cd2b7f1e5be06aea5c4'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/09-THE_BOY_DONE_WRONG_AGAIN.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','209.23275','Metal','the boy done wrong again','','','1999','mp3',128000,3347852,'2023-08-25 09:10:53','2023-08-25 09:10:53',88464679,'f3877c2532077939751cf5a3ab174fe1'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-If_You’re_Feeling_Sinister/10-JUDY_AND_THE_DREAM_OF_HORSES.mp3','If You?re Feeling Sinister','827a059380f7248c012f960e4b831993','Belle & Sebastian','','','173.07925','Metal','judy and the dream of horses','','','1999','mp3',128000,2769396,'2023-08-25 09:10:54','2023-08-25 09:10:54',88464680,'7c7cc03543c73c74b5456e71eafe1ac6'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/02-Expectations.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','214.46535','POP','expectations','','002','2008','mp3',160000,4642013,'2023-08-25 09:10:54','2023-08-25 09:10:54',88464681,'4b9924b2a4c1dedd0ede0bbea152b61d'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/03-She’s_Losing_It.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','142.31515','POP','she’s losing it','','003','2008','mp3',160000,3199029,'2023-08-25 09:10:54','2023-08-25 09:10:54',88464682,'04a8d2bfc20e424d843b6e3f7cfa548e'),(26452,'GDrive - scratch','A/test-libraries/scratch/An_Awesome_Wave/7-Dissolve_Me.flac','An Awesome Wave','4f2737a092f58da88a17c05ea241cf28','alt-J','','','239.01333333333','Folk/Rock','Dissolve Me','','07','2012','flac',995182.53932835,29732737,'2023-08-25 09:10:55','2023-08-25 09:10:55',88464683,'b0b50adf7d09af5f8acf4b30dc882148'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/04-You’re_Just_a_Baby.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','221.858','POP','you’re just a baby','','004','2008','mp3',160000,4789892,'2023-08-25 09:10:55','2023-08-25 09:10:55',88464684,'0633cb93da1bc442a2b9b88dcc75a4c2'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/05-Electronic_Renaissance.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','290.92575','POP','electronic renaissance','','005','2008','mp3',160000,6171231,'2023-08-25 09:10:55','2023-08-25 09:10:55',88464685,'709a1752bff9d93fcc1ada2bae6e390d'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/06-I_Could_Be_Dreaming.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','356.20575','POP','i could be dreaming','','006','2008','mp3',160000,7476828,'2023-08-25 09:10:56','2023-08-25 09:10:56',88464686,'89521ac172968c7cb9d664126b4b0b41'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/07-We_Rule_the_School.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','207.36','POP','we rule the school','','007','2008','mp3',160000,4499912,'2023-08-25 09:10:56','2023-08-25 09:10:56',88464687,'911a8ba11f616eac57876ecd13436bfc'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/08-My_Wandering_Days_Are_Over.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','325.8776','POP','my wandering days are over','','008','2008','mp3',160000,6870272,'2023-08-25 09:10:56','2023-08-25 09:10:56',88464688,'5a129c24101410804f962cb0900eff87'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/09-I_Don’t_Love_Anyone.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','236.7739','POP','i don’t love anyone','','009','2008','mp3',160000,5088212,'2023-08-25 09:10:57','2023-08-25 09:10:57',88464689,'069addd2917588668cfec01a7f186e65'),(26452,'GDrive - scratch','A/test-libraries/scratch/Belle_and_Sebastian-Tigermilk/10-Mary_Jo.mp3','Tigermilk','2f3b0691ab79d244e9ca63349b2ea61f','Belle and Sebastian','','','209.6849','POP','mary jo','','010','2008','mp3',160000,4546399,'2023-08-25 09:10:57','2023-08-25 09:10:57',88464690,'0021f1e6af5e30ab079b423550e407e0'),(26452,'GDrive - scratch','A/test-libraries/scratch/Various_Artists-The_Best_Northern_Soul_All-Nighter..._Ever!/01-THE_SNAKE.flac','The Best Northern Soul All-Nighter... Ever!','a8621040922a456fd2c1d1a7772f5c71','Various Artists','','','209.32','R&B','the snake','2','001','2006','flac',989852.09248997,25899480,'2023-08-25 09:22:43','2023-08-25 09:22:43',88465142,'d6623134e50e2ffb5ad7c08322aa4a6f'),(26452,'SM','/Music/123abc/11 Oul Tani Eyh (Say It Again).m4a','Ah W Noss','181dccd2281663b6939a94c233d8c6cc','ن123','','','250.82195011338','','anb','','11','','quicktime',833671.15160966,26166908,'2023-09-16 13:49:53','2023-09-16 13:49:53',88818667,'0c317b6f8279e40ac6daef951977a8aa')
INSERT INTO `rating` VALUES (26452,'song','GDrive - scratch:A/test-libraries/scratch/Various_Artists-The_Best_Northern_Soul_All-Nighter..._Ever!/01-THE_SNAKE.flac',5),(26452,'song','SM:/Music/123abc/11 Oul Tani Eyh (Say It Again).m4a',5);

Expected results

+----------------------------------+--------+
| title                            | rating |
+----------------------------------+--------+
| (Interlude 1)                    |   NULL |
| (Interlude 2)                    |   NULL |
| (Interlude 3)                    |   NULL |
| a certain romance                |   NULL |
| A Space Boy Dream                |   NULL |
| A Summer Wasting                 |   NULL |
| anb                              |      5 |
| Bloodflood                       |   NULL |
| Breezeblocks                     |   NULL |
| Chickfactor                      |   NULL |
| dancing shoes                    |   NULL |
| Dirty Dream Number Two           |   NULL |
| Dissolve Me                      |   NULL |
| Ease Your Feet In The Sea        |   NULL |
| electronic renaissance           |   NULL |
| expectations                     |   NULL |
| fake tales of san francisco      |   NULL |
| Fitzpleasure                     |   NULL |
| from the ritz to the rubble      |   NULL |
| Get Me Away from Here, I'm Dying |   NULL |
+----------------------------------+--------+

How can I force the query optimiser to perform the sort on fewer rows?

Answer 1

Replace

KEY `user_id` (`id`)

with

INDEX(id, title)

Normally one would say r.id = l.id instead of repeating the constant. I don't think it makes any difference to the Optimizer.

If you know that l can be limited to 20 before looking at r (or vice versa), then a derived table containing the LIMIT will [probably] run must faster.