php - MySQL "Copying to temp table"太慢

Question

我有以下查询:

SELECT COUNT( Siret ) AS  `NbEntr` ,  `Villes`.`Latitude` AS  `Latitude` ,      `Villes`.`Longitude` AS  `Longitude` , ( 6371 * ACOS( COS( RADIANS( 47.29473000 ) ) * COS(     RADIANS(  `Latitude` ) ) * COS( RADIANS(  `Longitude` ) - RADIANS( - 2.35991000 ) ) + SIN( RADIANS( 47.29473000 ) ) * SIN( RADIANS(  `Latitude` ) ) ) ) AS  `distance` 
FROM  `Villes` 
INNER JOIN  `Liste_Etablissements` ON  `CodeInsee` =  `Code_Insee` 
GROUP BY  `Code_Insee` 
HAVING distance <=30

大公式用于获取定义半径内某个点周围的所有城市( https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql )

这是查询的解释:

| id | select_type | table                | type   | possible_keys | key     | key_len | ref                                         | rows    | Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1  | SIMPLE      | Liste_Etablissements | ALL    | Code_Insee    | NULL    | NULL    | NULL                                        | 5596799 | Using temporary; Using filesort
| 1  | SIMPLE      | Villes               | eq_ref | PRIMARY       | PRIMARY | 15      | outilgeoloc.Liste_Etablissements.Code_Insee | 1       |

问题是，当我在 PhpMyAdmin 中执行此查询时，需要 6 秒才能执行，这很好(我认为？)，因为主表大约有 5 000 000 行，但是当我用我的 PHP 代码执行它时，它执行大约需要 30/40 秒。

当我查看查询执行过程中的过程时，我发现它在整个 30/40 秒内都在“复制到临时表”上，但是对于 Phpmyadmin，它在“发送数据”上。

我已经将 tmp_table_size 和 max_heap_table_size 设置为 256Mo，但查询执行时间仍然在 30/40s 左右。

那么我可以做些什么来加快查询的执行时间呢？我可以绕过“复制到临时表”步骤吗？

Answer 1

我找到了一个解决方案:

现在，我将查询分为两部分:首先，我获取特定半径周围的城市列表，然后，对于每个城市，我获取我的行数。

对于那些想看看我是怎么做到的人:

原文:

SELECT COUNT( Siret ) AS  `NbEntr`,  `Villes`.`Latitude` AS  `Latitude`,      `Villes`.`Longitude` AS  `Longitude`, ( 6371 * ACOS( COS( RADIANS( 47.29473000 ) ) * COS(       RADIANS(  `Latitude` ) ) * COS( RADIANS(  `Longitude` ) - RADIANS( - 2.35991000 ) ) + SIN( RADIANS( 47.29473000 ) ) * SIN( RADIANS(  `Latitude` ) ) ) ) AS  `distance` 
FROM  `Villes` 
INNER JOIN  `Liste_Etablissements` ON  `CodeInsee` =  `Code_Insee` 
GROUP BY  `Code_Insee` 
HAVING distance <=30

重构:

我先得到城市的坐标:

SELECT CodeInsee, Latitude, Longitude, ( 6371 * ACOS( COS( RADIANS( 47.29473000 ) ) * COS(       RADIANS(  `Latitude` ) ) * COS( RADIANS(  `Longitude` ) - RADIANS( - 2.35991000 ) ) + SIN( RADIANS( 47.29473000 ) ) * SIN( RADIANS(  `Latitude` ) ) ) ) AS  `distance` 
FROM Villes
HAVING distance <=30

然后，我的 Php 代码中有一个 foreach 循环，其中 foreach“CodeInsee”(这是我在“Villes”表中的主键)id 执行第二个查询:

SELECT COUNT(Code_Insee) as NbEntr
FROM Liste_Etablissements
WHERE Code_Insee = :codeinsee

我将 COUNT(Siret) 更改为 COUNT(Code_Insee) 因为那样的话，Mysql 只使用索引而不使用数据，这样速度更快。

并且将这两个查询分开，mysql 不再使用慢速临时表。

这是一篇对我帮助很大的文章(抱歉，它是法语的):http://dasini.net/blog/2009/02/18/optimisation-de-requetes-comprendre-loptimiseur-de-mysql/

还是谢谢你的帮助:D