我在场馆表中有一个场馆列表,在位置表中有一个城市/州列表。场地与我所在组织独有的区号关联,称为SOYID。SOYID由一个地理区域组成-Locations表中的每一行都有一个城市、州和相应的SOYID。有些场馆行有一个SOYID,其他的没有;对于那些没有,我需要找到列出的城市和州的SOYID。我只想在一个特定的大豆选择那些地点。
这个查询可以工作,但是加载它需要几秒钟;我认为我没有正确地编写查询。目前场馆有140排左右,位置有40000个。
$sql = "SELECT DISTINCT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
FROM Venues AS a LEFT JOIN Locations AS c ON a.City = c.city
WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
OR ((c.city = a.City) AND (c.state = a.StateAbbr) AND (c.SOYID = '" . mysql_real_escape_string($SOYID) . "'))
ORDER BY a.Name ASC";
最佳答案
每当您在WHERE子句中引用来自左联接表的列(在您的特定情况下,c.state
和c.SOYID
时),都会强制该联接的行为类似于内部联接。相反,将这些测试作为联接条件的一部分:
"SELECT DISTINCT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
FROM Venues AS a
LEFT JOIN Locations AS c
ON a.City = c.city
AND a.StateAbbr = c.state
AND c.SOYID = '" . mysql_real_escape_string($SOYID) . "'
WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
OR c.SOYID IS NOT NULL /* LEFT JOIN found a matching row */
ORDER BY a.Name ASC"
编辑:根据注释,此版本应允许您删除
DISTINCT
要求:"SELECT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
FROM Venues AS a
WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
OR EXISTS(SELECT NULL
FROM Locations AS c
WHERE a.City = c.city
AND a.StateAbbr = c.state
AND c.SOYID = '" . mysql_real_escape_string($SOYID) . "')
ORDER BY a.Name ASC"
关于php - 优化小型MySQL连接查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7350021/