我无缘无故遇到了以下问题。我有2张桌子:

isrc(约23000行):

Field           Type           Null    Default    Extra
isrc_ID         int(11)         NO      PRI     auto_increment
ISRC            varchar(25)     NO      UNI
Track_Name      varchar(255)    YES
Track_Time      time            YES     00:00:00
ArtistName      varchar(255)    YES
TitleVersion    varchar(255)    YES
Date_update     timestamp       NO      CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP


isrc_performer(901行):

Field               Type           Null    Default    Extra
Perf_ID             int(11)         NO      PRI     auto_increment
isrc_ID             int(11)         NO
PerformerName       varchar(255)    YES
PerformerCategory   varchar(255)    YES
PerformerRole       varchar(255)    YES
date_update         timestamp       NO      CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP


并且以下简单查询运行时间过长:

SELECT
isrc.ISRC,
isrc.Track_Name,
isrc.ArtistName,
isrc.TitleVersion,
isrc.Track_Time,
`isrc_performer`.`PerformerName` ,
`isrc_performer`.`PerformerCategory` ,
`isrc_performer`.`PerformerRole` ,
`isrc`.`isrc_ID`,
`isrc_performer`.`Perf_ID`

FROM `isrc`

LEFT JOIN `isrc_performer` ON (isrc_performer.isrc_ID = isrc.isrc_ID)

ORDER BY   isrc_ID     desc LIMIT 0 , 10


运行时间-5.117秒

对每个表的单独请求:

SELECT
isrc.ISRC,
isrc.Track_Name,
isrc.ArtistName,
isrc.TitleVersion,
isrc.Track_Time

FROM `isrc`

ORDER BY   isrc_ID     desc LIMIT 0 , 10


运行时间-0.000秒

SELECT
`isrc_performer`.`PerformerName` ,
`isrc_performer`.`PerformerCategory` ,
`isrc_performer`.`PerformerRole`,
`isrc_performer`.`Perf_ID`

FROM `isrc_performer`

ORDER BY   Perf_ID     desc LIMIT 0 , 10


运行时间-0.000秒

谁能以崭新的眼光指向我正确的方向,为什么会这样呢?

最佳答案

将索引添加到isrc_performer表:

ALTER TABLE isrc_performer ADD INDEX (isrc_ID);


否则,它必须对表执行完整扫描,以在连接时查找具有匹配的isrc_ID的行。

关于mysql - MySQL查询运行时间过长(简单的LEFT JOIN),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43693064/

10-12 06:06