Sunday 22 September 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

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...