I've done many regular database queries in the past, but now I have a database of about 1.5 million entries and I need to be able to do a quick search for live update.
My database is all US zip code and canadian postal codes with longitute, latitude, city ... I need to be able to pull one single zip or postal code in a fraction of a second and send it back (with ajax) to the web page. Therefore when the user enter the postalcode/zip, the country, city and state/province fill in automaticly.
Currently I use the following query :
$sql = "SELECT city, province, country
FROM postalcode
WHERE PostalCode='$zip'";
I've never used an index before now. How could I create one and replace my query to get the same result?
(For extra points! : will the index be backed up with mysqldump?)
Thank You!
Answer
Finally I've created my index on the postal code directly from mySQL command line :
CREATE INDEX postal_index ON postalcode(postalcode);
My search for a code went from 4.89sec to 0.00 !!!!!!!
No comments:
Post a Comment