Optimize Inner Join SQL Query

Question

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.

have you index on the column involved in on clause ? and ville_population_2012? — ScaisEdge
– ScaisEdge, Commented Mar 6, 2017 at 15:29
can you please add the table structures as well (what columns are of which type). — Olli
– Olli, Commented Mar 6, 2017 at 15:31
Indexing on village name could work, but for lat/long you need a spatial index — Andomar
– Andomar, Commented Mar 6, 2017 at 15:51

Andomar · Accepted Answer · 2017-03-06 15:49:57Z

1

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

edited Mar 6, 2017 at 15:49

answered Mar 6, 2017 at 15:31

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pablo DelaNoche Over a year ago

It returns empty results !

Andomar Over a year ago

Your'e right, moving the limit 1 to the inner query was incorrect! Glad the index suggestion helped

Pablo DelaNoche · Accepted Answer · 2017-03-06 15:39:14Z

0

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.

answered Mar 6, 2017 at 15:39

Pablo DelaNoche

6771 gold badge12 silver badges30 bronze badges

1 Comment

Strawberry Over a year ago

You get one index per table, so you may as well add a covering index on (lat,lon)

Collectives™ on Stack Overflow

Optimize Inner Join SQL Query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related