在我们的应用程序中,我们使用以下3个表:
- 类别
- 城市(category_id)
- city_data(距离,city_id)
$q = "SELECT a.id as aid,a.distance as adistance, b.id as bid,b.distance as bdistance FROM city_data as a JOIN city_data as b on a.id != b.id JOIN cities AS a_cities ON a.city_id = a_cities.id JOIN cities AS b_cities ON b.city_id = b_cities.id WHERE (a_cities.category_id='".$_GET["c"]."' AND b_cities.category_id='".$_GET["c"]."') AND abs(a.distance - b.distance) < 100 ORDER BY RAND() LIMIT 1";
表城市、city_data具有相同的行数-几乎为5.000。上面的查询大约需要45秒,这太可怕了。更糟糕的是,这些表应该还有5.000行,总共有10.000行。。。
我想问你任何方法,如何减少执行上述查询的时间。。。45s是不可接受的。。。
我可以选择解决这个问题吗?
编辑:感谢您的建议,我删除了ORDER BY RAND()部分,时间确实较低,约为22秒,但对于通常使用来说仍然太高
确保已在联接列(cities.city_id、city_data.id、cities.category_id)上创建索引
我不知道你的表是如何设计的,但你应该在一个(可能是另一个)表中保留city1到city2的距离信息,去掉最后两个联接,并在另一个查询中获得结果的类别信息。
如之前给出的示例(计算400个城市之间的距离并为其优化MySQL?)
SELECT c1.name, c2.name, cd.dist
FROM cities_dist cd
INNER JOIN cities c1 ON cd.city1 = c1.id
INNER JOIN cities c2 ON cd.city2 = c2.id
WHERE cd.city1 = your_id
OR cd.city2 = your_id
ORDER BY cd.dist ASC
请确保您有正确的索引和字段类型定义。
为什么要加入?
FROM city_data as a JOIN city_data as b on a.id != b.id
您将city_data
表的数据与同一表的数据连接在一起,而在同一表中,它们之间的唯一关系不匹配。我想这就是为什么你的查询如此缓慢的原因。
分解查询。将每个子查询转换为一个视图。分别运行它们。这样可以提高性能。运行一个非常复杂的长查询通常比运行一个简化查询慢。同时尽可能限制每个子查询的结果。然后,也许你可以使用联合来加入结果。这将是我的第一个方法。
将所有这些数据拉回到PHP中并在那里进行循环/距离计算可能是有意义的——这在代码中可能比在DB中更快。