我无缘无故遇到了以下问题。我有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/