缓慢的SQL查询:在两个不同的联接中使用同一张表会导致查询变慢10倍！ [英] Slow SQL query: using the same table in two different joins causes query to become 10x slower!
问题描述
真的希望某种性能的高手可以向我解释为什么单次连接会导致查询速度降低10倍. (此外，请不要嘲笑此查询的大小！我想获取数据库中的整个目录以通过一个查询进行输出.我不确定将其分解为较小的查询是否会更快，但是似乎不正确.)
Really hoping that some kind of performance whiz can explain to me why a single join is causing the query to become 10x slower. (Also, please don't laugh about the size of this query! I wanted to get out the whole catalog in my database to output with one query. I'm not sure if it would be faster to break it into smaller queries but that doesn't seem right.)
SELECT `c`.`categoryID`,
`cl`.`name` AS `category_name`,
`v`.*,
TRUE AS `categoried`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`,
`vl`.*,
GROUP_CONCAT(DISTINCT kl.name) AS `keywords`
FROM `tblCategories` AS `c`
INNER JOIN `tblCategoryLocalisedData` AS `cl` ON c.categoryID = cl.categoryID
LEFT JOIN `tblCategoryDurations` AS `cd` ON c.categoryID = cd.categoryID
LEFT JOIN `tblCategoryRules` AS `cr` ON c.categoryID = cr.categoryID
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
INNER JOIN `tblVideoLocalisedData` AS `vl` ON vl.videoID = v.videoID
LEFT JOIN `tblVideoKeywords` AS `vk` ON v.videoID = vk.videoID
LEFT JOIN `tblKeywords` AS `k` ON vk.keywordID = k.keywordID
LEFT JOIN `tblKeywordLocalisedData` AS `kl` ON kl.keywordID = k.keywordID
INNER JOIN `tblLanguages` AS `l`
WHERE (cv.disabled IS NULL)
AND (cd.start_date < NOW() OR cd.start_date IS NULL)
AND (cd.end_date > NOW() OR cd.end_date IS NULL)
AND (cr.name IS NULL)
AND (l.languageID = cl.languageID OR cl.languageID IS NULL)
AND (l.languageID = kl.languageID OR kl.languageID IS NULL)
AND (l.languageID = vl.languageID OR vl.languageID IS NULL)
AND (l.iso_639_1 = 'en')
GROUP BY `v`.`videoID`, `c`.`categoryID`
ORDER BY `c`.`categoryID` ASC
当我运行上面的查询时，它需要1秒钟完成.我尝试在上面运行一个EXPLAIN，它给了我这个:
When I run the above query it takes 1 whole second to finish. I tried running an EXPLAIN on it and it gave me this:
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | cv | ALL | fk_tblCategoryVideos_tblCategories1,fk_tblCategoryVideos_tblVideos1 | NULL | NULL | NULL | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | db.cv.categoryID | 1 | Using index |
| 1 | SIMPLE | cd | ref | fk_tblCategoryDurations_tblCategories | fk_tblCategoryDurations_tblCategories | 4 | db.cv.categoryID | 1 | Using where |
| 1 | SIMPLE | cr | ref | fk_tblCategoryRules_tblCategories1 | fk_tblCategoryRules_tblCategories1 | 4 | db.cv.categoryID | 1 | Using where; Not exists |
| 1 | SIMPLE | vt | ref | fk_tblVideoTerritories_tblVideos1,fk_tblVideoTerritories_tblTerritories1 | fk_tblVideoTerritories_tblVideos1 | 4 | db.cv.videoID | 1 | Using where |
| 1 | SIMPLE | t_v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.territoryID | 1 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.videoID | 1 | Using where |
| 1 | SIMPLE | vk | ref | fk_tblVideoKeywords_tblVideos1 | fk_tblVideoKeywords_tblVideos1 | 4 | db.cv.videoID | 6 | |
| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 4 | db.vk.keywordID | 1 | Using index |
| 1 | SIMPLE | kl | ref | fk_tblKeywordLocalisedData_tblKeywords1 | fk_tblKeywordLocalisedData_tblKeywords1 | 4 | db.k.keywordID | 1 | |
| 1 | SIMPLE | cl | ALL | fk_tblCategoryLocalisedData_tblCategories1,fk_tblCategoryLocalisedData_tblLanguages1 | NULL | NULL | NULL | 5 | Using where; Using join buffer |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | db.cl.languageID | 1 | Using where |
| 1 | SIMPLE | ct | ALL | fk_tblCategoryTerritories_tblCategories1,fk_tblCategoryTerritories_tblTerritories1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | vl | ALL | fk_tblVideoLocalisedData_tblLanguages1,fk_tblVideoLocalisedData_tblVideos1 | NULL | NULL | NULL | 9 | Using where; Using join buffer |
| 1 | SIMPLE | t_c | eq_ref | PRIMARY | PRIMARY | 4 | db.ct.territoryID | 1 | |
+----+-------------+-------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
但是我不知道那意味着什么.我该如何解决?值得庆幸的是，我确实知道查询的哪些部分会导致大量的速度下降.如果我删除了从tblVideoTerritories(vt)到tblTerritories(t_v)或tblCategoryTerritories(ct)到tblTerritories(t_c)的联接，那么一切都会大大加快.我以为开始可能是因为GROUP_CONCAT或DISTINCT，但我尝试删除它们，但几乎没有任何改变.似乎性能问题是由于两次连接到同一表'tblTerritories'引起的.如果我只有这些连接中的一个，则查询只需要0.1秒或0.2秒即可运行-这仍然是很长的时间，但这是一个更好的开始！
However I have no idea what that means. How do I troubleshoot this? I do, thankfully, know which parts of the query cause the massive slow-down. If I remove the joins from either tblVideoTerritories (vt) to tblTerritories (t_v) or tblCategoryTerritories (ct) to tblTerritories (t_c) then everything speeds up considerably. I thought to begin with it might have been because of the GROUP_CONCAT or DISTINCT but I tried removing these and it made barely any change. It seems that the performance problem is caused by joining to the same table 'tblTerritories' twice. If I only have one of those joins the query only takes 0.1 seconds or 0.2 seconds to run -- this is still a long time but it's a better start!
我想知道如何解决此性能问题? 为什么两次连接到同一张表会导致查询花费10倍的时间?！
What I want to know is how can I fix this performance problem? Why would joining to the same table twice cause the query to take 10x as long?!
感谢您的帮助！
修改: 在tblVideoTerritories上的SHOW CREATE TABLE给我这个:
edit: A SHOW CREATE TABLE on tblVideoTerritories gives me this:
CREATE TABLE `tblVideoTerritories` (
`videoTerritoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`videoID` int(10) unsigned NOT NULL,
`territoryID` int(10) unsigned NOT NULL,
PRIMARY KEY (`videoTerritoryID`),
KEY `fk_tblVideoTerritories_tblVideos1` (`videoID`),
KEY `fk_tblVideoTerritories_tblTerritories1` (`territoryID`),
CONSTRAINT `fk_tblVideoTerritories_tblTerritories1` FOREIGN KEY (`territoryID`) REFERENCES `tblTerritories` (`territoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblVideoTerritories_tblVideos1` FOREIGN KEY (`videoID`) REFERENCES `tblVideos` (`videoID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
tblCategoryTerritories上的SHOW CREATE TABLE给我:
A SHOW CREATE TABLE on tblCategoryTerritories gives me this:
CREATE TABLE `tblCategoryTerritories` (
`categoryTerritoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`categoryID` int(10) unsigned NOT NULL,
`territoryID` int(10) unsigned NOT NULL,
PRIMARY KEY (`categoryTerritoryID`),
KEY `fk_tblCategoryTerritories_tblCategories1` (`categoryID`),
KEY `fk_tblCategoryTerritories_tblTerritories1` (`territoryID`),
CONSTRAINT `fk_tblCategoryTerritories_tblCategories1` FOREIGN KEY (`categoryID`) REFERENCES `tblCategories` (`categoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblCategoryTerritories_tblTerritories1` FOREIGN KEY (`territoryID`) REFERENCES `tblTerritories` (`territoryID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
tblTerritories上的SHOW CREATE TABLE给我:
A SHOW CREATE TABLE on tblTerritories gives me this:
CREATE TABLE `tblTerritories` (
`territoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`iso_3166_1_alpha_2` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`iso_3166_1_alpha_3` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`defaultLanguageID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`territoryID`),
KEY `fk_tblTerritories_tblLanguages1` (`defaultLanguageID`),
KEY `iso_3166_1_alpha_2` (`iso_3166_1_alpha_2`),
CONSTRAINT `fk_tblTerritories_tblLanguages1` FOREIGN KEY (`defaultLanguageID`) REFERENCES `tblLanguages` (`languageID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
edit2: 两次加入同一地区的原因是我需要使用查询顶部的GROUP_CONCAT生成两个单独的地区列表.我需要一个视频，一个视频，用于其所属类别.
edit2: The reason for joining twice to the same territory is that I need to generate two separate lists of territories using the GROUP_CONCAT at the top of the query. I need one for a video and one for the category it belongs to.
edit3: 有趣的是，如果我将查询缩减到最简单的程度，那么即使两次连接到同一张表，它也非常快(0.00秒):
edit3: Interestingly if I cut down the query right down to its bare bones then it is very fast (0.00 seconds) even while joining to the same table twice:
SELECT `c`.`categoryID`,
`v`.`videoID`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`
FROM `tblCategories` AS `c`
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
GROUP BY `v`.`videoID`, `c`.`categoryID`
如果我从使用WHERE临时切换为开，那我仍然需要0.98秒的查询时间:
edit4: If I switch from using the WHERE as a makeshift ON then I still have a query which takes 0.98 seconds:
SELECT `c`.`categoryID`,
`cl`.`name` AS `category_name`,
`v`.*,
TRUE AS `categoried`,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS `video_territories`,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS `category_territories`,
`vl`.*,
GROUP_CONCAT(DISTINCT kl.name) AS `keywords`
FROM `tblCategories` AS `c`
INNER JOIN `tblCategoryLocalisedData` AS `cl` ON c.categoryID = cl.categoryID
LEFT JOIN `tblCategoryDurations` AS `cd` ON c.categoryID = cd.categoryID
LEFT JOIN `tblCategoryRules` AS `cr` ON c.categoryID = cr.categoryID
LEFT JOIN `tblCategoryVideos` AS `cv` ON c.categoryID = cv.categoryID
LEFT JOIN `tblVideos` AS `v` ON cv.videoID = v.videoID
LEFT JOIN `tblVideoTerritories` AS `vt` ON vt.videoID = v.videoID
LEFT JOIN `tblCategoryTerritories` AS `ct` ON ct.categoryID = c.categoryID
INNER JOIN `tblTerritories` AS `t_v` ON t_v.territoryID = vt.territoryID
INNER JOIN `tblTerritories` AS `t_c` ON t_c.territoryID = ct.territoryID
INNER JOIN `tblVideoLocalisedData` AS `vl` ON vl.videoID = v.videoID
LEFT JOIN `tblVideoKeywords` AS `vk` ON v.videoID = vk.videoID
LEFT JOIN `tblKeywords` AS `k` ON vk.keywordID = k.keywordID
LEFT JOIN `tblKeywordLocalisedData` AS `kl` ON kl.keywordID = k.keywordID
INNER JOIN `tblLanguages` AS `l` ON (l.languageID = cl.languageID OR cl.languageID IS NULL) AND (l.languageID = kl.languageID OR kl.languageID IS NULL) AND (l.languageID = vl.languageID OR vl.languageID IS NULL)
WHERE (cv.disabled IS NULL)
AND (cd.start_date < NOW() OR cd.start_date IS NULL)
AND (cd.end_date > NOW() OR cd.end_date IS NULL)
AND (cr.name IS NULL) AND (l.iso_639_1 = 'en')
GROUP BY `v`.`videoID`, `c`.`categoryID`
ORDER BY `c`.`categoryID` ASC
如果删除与关联相关的关键字，查询将在0.09秒内发生...删除tblKeyword和tblKeywordLocalisedData，但保留tblVideoKeywords给我0.80秒.删除tblVideoKeywords可使我得到0.09秒的时间.
edit5: If I remove the keyword related joins the query happens in 0.09 seconds... Removing the tblKeyword and tblKeywordLocalisedData but leaving tblVideoKeywords gives me 0.80 seconds. Removing tblVideoKeywords gives me 0.09 seconds.
但是它似乎有索引，所以我还是不明白:
But it seems to have indexes so yet again I don't get it:
CREATE TABLE `tblVideoKeywords` (
`videoKeywordID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`videoID` int(10) unsigned NOT NULL,
`keywordID` int(10) unsigned NOT NULL,
PRIMARY KEY (`videoKeywordID`),
KEY `fk_tblVideoKeywords_tblVideos1` (`videoID`),
KEY `fk_tblVideoKeywords_tblKeywords1` (`keywordID`),
CONSTRAINT `fk_tblVideoKeywords_tblKeywords1` FOREIGN KEY (`keywordID`) REFERENCES `tblKeywords` (`keywordID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_tblVideoKeywords_tblVideos1` FOREIGN KEY (`videoID`) REFERENCES `tblVideos` (`videoID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
edit6: 使用DRapp提供的查询可使一切变得更快.现在，他的查询说明为我提供了
edit6: Using the query provide by DRapp makes everything a lot faster. The EXPLAIN of his query now gives me:
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | cl | ALL | fk_tblCategoryLocalisedData_tblCategories1,fk_tblCategoryLocalisedData_tblLanguages1 | NULL | NULL | NULL | 5 | Using where; Using join buffer |
| 1 | SIMPLE | lang_cl | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | cd | ref | fk_tblCategoryDurations_tblCategories | fk_tblCategoryDurations_tblCategories | 4 | db.c.categoryID | 1 | |
| 1 | SIMPLE | cr | ref | fk_tblCategoryRules_tblCategories1 | fk_tblCategoryRules_tblCategories1 | 4 | db.c.categoryID | 1 | Using where; Not exists |
| 1 | SIMPLE | cv | ALL | fk_tblCategoryVideos_tblCategories1,fk_tblCategoryVideos_tblVideos1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | ct | ALL | fk_tblCategoryTerritories_tblCategories1,fk_tblCategoryTerritories_tblTerritories1 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | t_c | eq_ref | PRIMARY | PRIMARY | 4 | db.ct.territoryID | 1 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | db.cv.videoID | 1 | Using where |
| 1 | SIMPLE | vt | ref | fk_tblVideoTerritories_tblVideos1,fk_tblVideoTerritories_tblTerritories1 | fk_tblVideoTerritories_tblVideos1 | 4 | db.v.videoID | 1 | Using where |
| 1 | SIMPLE | t_v | eq_ref | PRIMARY | PRIMARY | 4 | db.vt.territoryID | 1 | |
| 1 | SIMPLE | vl | ALL | fk_tblVideoLocalisedData_tblLanguages1,fk_tblVideoLocalisedData_tblVideos1 | NULL | NULL | NULL | 9 | Using where; Using join buffer |
| 1 | SIMPLE | lang_vl | eq_ref | PRIMARY | PRIMARY | 4 | db.vl.languageID | 1 | Using where |
| 1 | SIMPLE | vk | ALL | fk_tblVideoKeywords_tblVideos1,fk_tblVideoKeywords_tblKeywords1 | NULL | NULL | NULL | 15 | Using where; Using join buffer |
| 1 | SIMPLE | k | eq_ref | PRIMARY | PRIMARY | 4 | db.vk.keywordID | 1 | Using where; Using index |
| 1 | SIMPLE | kl | ref | fk_tblKeywordLocalisedData_tblKeywords1,fk_tblKeywordLocalisedData_tblLanguages1 | fk_tblKeywordLocalisedData_tblKeywords1 | 4 | db.k.keywordID | 1 | Using where |
| 1 | SIMPLE | lang_kl | eq_ref | PRIMARY | PRIMARY | 4 | db.kl.languageID | 1 | Using where |
+----+-------------+---------+--------+--------------------------------------------------------------------------------------+-----------------------------------------+---------+------------------------+------+----------------------------------------------+
17 rows in set (0.01 sec)
推荐答案
对于我已经回答的其他几个类似问题，只需添加"STRAIGHT_JOIN"，稍加重组即可提供帮助.查询优化器实际上将尝试为您考虑所有表，尝试查找记录较少的表，然后将记录与较大的表连接起来，从而造成混乱.发生这种情况的原因是，我在对15个以上的子表进行查找时对14个以上的百万条记录进行了政府查询，这与您在此处进行的操作非常相似.它执行了一个查询，该查询在专用的独立服务器上运行了30多个小时，并将其挂起(最多2个小时)...请尝试以下操作:
Per several other questions I've answered similarly, just by adding a "STRAIGHT_JOIN" and slight restructuring can help. The query optimizer will actually try to think for you with respect to all the tables, try to find a table with less records and join that to the larger thus causing total chaos. This happened when I was doing gov't data querying of 14+ million records with lookups to 15+ sub-tables... Very similar to what you have going on here. It took a query that was running 30+ hours on a dedicated, stand-alone server and hung it, down to under 2 hours... Try the following:
除了对联接进行视觉上的清理/排序以外，我还采用了一些NOW()vs NULL并将它们移入联接中.如果查询左联接并将日期作为联接限定符的一部分，则会排除那些超出范围的记录，从而保留NULL结果集或有效条目，而无需将该限定符加倍.
In addition to some visual cleansing / ordering of the joins to what I am used to, I've also taken some of the NOW() vs NULL and moved them into the join. If you query a left join and have the dates as part of the join qualifier, you'll have those records out of bounds excluded and thus leave either a NULL resultset or a valid entry, no need to double-up that qualifier.
SELECT STRAIGHT_JOIN
c.categoryID,
cl.name AS category_name,
v.*,
TRUE AS categoried,
GROUP_CONCAT(DISTINCT t_v.iso_3166_1_alpha_2) AS video_territories,
GROUP_CONCAT(DISTINCT t_c.iso_3166_1_alpha_2) AS category_territories,
vl.*,
GROUP_CONCAT(DISTINCT kl.name) AS keywords
FROM
tblCategories AS c
INNER JOIN tblCategoryLocalisedData AS cl
ON c.categoryID = cl.categoryID
INNER JOIN tblLanguages AS lang_cl
ON l.languageID = lang_cl.languageID
AND lang_cl.iso_639_1 = 'en'
LEFT JOIN tblCategoryDurations AS cd
ON c.categoryID = cd.categoryID
AND cd.start_date < NOW()
AND cd.end_date > NOW()
LEFT JOIN tblCategoryRules AS cr
ON c.categoryID = cr.categoryID
LEFT JOIN tblCategoryVideos AS cv
ON c.categoryID = cv.categoryID
LEFT JOIN tblCategoryTerritories AS ct
ON c.categoryID = ct.categoryID
INNER JOIN tblTerritories AS t_c
ON ct.territoryID = t_c.territoryID
LEFT JOIN tblVideos AS v
ON cv.videoID = v.videoID
LEFT JOIN tblVideoTerritories AS vt
ON v.videoID = vt.videoID
INNER JOIN tblTerritories AS t_v
ON vt.territoryID = t_v.territoryID
INNER JOIN tblVideoLocalisedData AS vl
ON v.videoID = vl.videoID
INNER JOIN tblLanguages AS lang_vl
ON vl.languageID = lang_vl.languageID
AND lang_vl.iso_639_1 = 'en'
LEFT JOIN tblVideoKeywords AS vk
ON v.videoID = vk.videoID
LEFT JOIN tblKeywords AS k
ON vk.keywordID = k.keywordID
LEFT JOIN tblKeywordLocalisedData AS kl
ON k.keywordID = kl.keywordID
INNER JOIN tblLanguages AS lang_kl
ON kl.languageID = lang_kl.languageID
AND lang_kl.iso_639_1 = 'en'
WHERE
( cv.disabled IS NULL)
AND ( cr.name IS NULL)
GROUP BY
v.videoID,
c.categoryID
ORDER BY
c.categoryID ASC
上面我解释过的
STRAIGHT_JOIN基本上告诉优化器不要为我考虑" ....按照我告诉您的顺序进行查询.在这种情况下，请使用"tblCategories"作为主表并将其他所有内容链接起来.即使有说明，优化器也可能会变慢，并在下次运行查询时尝试另一种方法.因此，它可以尝试首先使用语言"表，并在其他表中进行后向填充并阻塞.此外，通过将"AND"部分(例如日期)直接指向那些左联接，这些联接将简化WHERE，如您所见……就像您在NULL或存在NULL的位置中那样，仅将其应用于该特定联接. .保持地方清洁.
STRAIGHT_JOIN as I explained above basically tells the optimizer, "dont think for me".... do the query in the order I've told you to. In this case, using "tblCategories" as the primary table and linking everything else off that. The optimizer, even with the explain may try be slow and try a different approach the next time you run the query. So, it could try to use the Languages table first and do back-paddling through the other tables and choke. Also, by having the "AND" portions such as the dates direct to those left joins, those joins simplify the WHERE as you see... as you had in the where for NULL or it existed, just applied to that particular join... keeps the where clean.
此外，通过使关系直接和缩进与其所加入的对象相对应，可以更容易地理解与何处链接的内容...
Additionally, by keeping the relationships direct and indented respective to what they were joining to, its easier to understand what is linked to where...
我还希望看到最终的"EXPLAIN"，并查看其中的内容.
I would additionally like to see the final "EXPLAIN" and see what it comes up with.
这篇关于缓慢的SQL查询:在两个不同的联接中使用同一张表会导致查询变慢10倍！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
