CODEWITHSUNDEEP
mysqlsql
Pablo DelaNoche
Pablo DelaNoche

Reputation: 677

Optimize Inner Join SQL Query

I want to search in my database the closest city from lat-long & range. To do that, I use this query :

SELECT ville_nom_reel FROM inspitravel.villes_france_free 
WHERE ville_latitude_deg BETWEEN 48.0462165495 AND 48.9471174505 
AND ville_longitude_deg BETWEEN -0.45045045045 AND 0.45045045045

This query was executed in 0.0123 sec.

 Now I would like to Search in another database and retrieve 5 more fields from ville_nom_reel field from 1st Query. To do that, I used INNER JOIN :

SELECT villes_france_free.ville_nom_reel, villes_booking.full_name, villes_booking.number_of_hotels, villes_booking.deeplink  FROM inspitravel.villes_france_free 
INNER JOIN inspitravel.villes_booking ON villes_france_free.ville_nom_reel = villes_booking.full_name 
WHERE ville_latitude_deg BETWEEN 48.0462165495 AND 48.9471174505 
AND ville_longitude_deg BETWEEN -0.45045045045 AND 0.45045045045 
AND ville_population_2012 > 3000 Limit 1

Now this query is executed in 4 seconds ...
How Can I Optimize this ? Thank you.

Upvotes: 1

Views: 4550

Answers (2)

Andomar
Andomar

Reputation: 238296

You could try to hin that the lat/long should be evaluated first? Maybe:

SELECT  * 
FROM    (
        SELECT  *
        FROM    inspitravel.villes_france_free 
        WHERE   ville_latitude_deg BETWEEN 48.0462165495 AND 48.9471174505 
                AND ville_longitude_deg BETWEEN -0.45045045045 AND 0.45045045045 
        ) villes
JOIN    inspitravel.villes_booking 
ON      villes.ville_nom_reel = villes_booking.full_name 
WHERE   ville_population_2012 > 3000
LIMIT   1

Upvotes: 1

Pablo DelaNoche
Pablo DelaNoche

Reputation: 677

Thank you for your help.
First I removed * by all my needed field names. Thanks @apomene

SELECT villes_france_free.ville_nom_reel, villes_booking.full_name, villes_booking.number_of_hotels, villes_booking.deeplink  FROM inspitravel.villes_france_free 
INNER JOIN inspitravel.villes_booking ON villes_france_free.ville_nom_reel = villes_booking.full_name 
WHERE ville_latitude_deg BETWEEN 48.0462165495 AND 48.9471174505 
AND ville_longitude_deg BETWEEN -0.45045045045 AND 0.45045045045 
AND ville_population_2012 > 3000 Limit 1

Then, Like you said, I had to Index columns involved in ON clause.
 Thanks @scaisEdge Now this query is executed in 0.100 sec ! Perfect.

Upvotes: 0

Related Questions