MySQL "Copying to temp table" too slow

Question

I have the following query:

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

The big formula is used to get all the city about a certain point within a defined radius (https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql)

Here is the Explain of the query:

| 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       |

The problem is that when I execute this query in PhpMyAdmin, it takes 6s to execute which is fine (I think ?) because the main table is around 5 000 000 lines, but when I execute it with my PHP code, it takes about 30/40 second to execute.

When I look at the process during the execution of the query, I see that it is on "Copying to temp table" during the whole 30/40s but with Phpmyadmin, it is on "Sending data".

I have setthe tmp_table_size and max_heap_table_size to 256Mo but the query execution time is still around 30/40s.

So what can I do to speed up the execution time of the query ? Can I bypass the "Copying to temp table" step ?

Part of the problem is the cost of executing the distance calculation: creating a bounding box so that you only select rows between certain latitudes and longitudes with a WHERE clause means that you'd only be calculating the distance for a subset of your database table - http://stackoverflow.com/questions/5236921/geo-search-distance-in-php-mysql-performance/5238667#5238667 — Mark Baker, Aug 20 '14 at 15:47

Zarwag · Accepted Answer · 2014-08-21T10:13:39.700

I found a solution:

Now, I have divided my query in 2 parts: First I get the list of cities around a certain radius, and after, for each city, i get the count of my lines.

For those who want to see how I did it:

Original:

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

Refactored:

I get the coordinates of the cities first:

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

And then, I have a foreach loop in my Php code where, foreach "CodeInsee" (which is my primary key in the "Villes" table) id do the second query:

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

I changed COUNT(Siret) for COUNT(Code_Insee) because with that, Mysql only use the index and not the data and this is faster.

And with these two query separated, mysql do not use anymore the slow temp table.

Here is an article which helped me a lot (sorry, it's in french): http://dasini.net/blog/2009/02/18/optimisation-de-requetes-comprendre-loptimiseur-de-mysql/

Thank you anyway for your help :D

MySQL "Copying to temp table" too slow

1 Answers1