Sunday, September 22, 2019

mysql - How to create an index for a large DB table



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

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...