CODEWITHSUNDEEP
mysqldatabaseoptimization
Jacob Riches
Jacob Riches

Reputation: 227

My Mysql select query runs slower each time it is run

I have 2 tables, People and postcodelatlng.
I have around 2,300 records in people which include their postcodes, as well as two empty columns for Lat & Lng coordinates.

postcodelatlng is a table with around 1.6 million rows containing every single postcode in the UK as well as their respective coordinates.

I am trying to fill out the coordinates in the people table by matching the postcodes. This works fine when not using a limit roughly 0.15 second for each person. Unfortunately, as soon as I put the limit on, each time I run the query, the execution time almost doubles.

SELECT people.ID, people.Postcode, postcodelatlng.latitude, postcodelatlng.longitude 
FROM people 
INNER JOIN postcodelatlng 
ON people.Postcode=postcodelatlng.postcode 
LIMIT 30

Upvotes: 1

Views: 62

Answers (1)

boppy
boppy

Reputation: 1908

I would recommend to add two indexes, since 0.15 sec per row seems a high number. One on each Postcode Field:

CREATE INDEX ppl_postcode ON people(Postcode) using HASH;
CREATE INDEX postcode_ll ON postcodelatlng(postcode) using HASH;

It should run much faster now.

Upvotes: 1

Related Questions