How to create an index for a large DB table

Question

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!

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

You don't replace the query. The index would be used by the optimizer to fetch your query. The index resides on the server and is not part of mysqldump. It will be in the DDL for your table though.

See How do I add indices to MySQL tables?. This assumes you know which column to put your index on (hint: PostalCode)

edited May 23 '17 at 10:32

Community

1
1

answered Mar 14 '13 at 01:00

Kermit

33,827
13
85
121

+1 for the bonus question! Thanks! I'll look up the other post you sugested! – Daniel Berthiaume Mar 14 '13 at 01:11

score 0 · Answer 2 · answered Mar 14 '13 at 01:01

0

Add the index to the PostalCode field and see if the performance improves.

I usually use phpMyAdmin to create my indices, but you can use the command line as suggested by another posting.

answered Mar 14 '13 at 01:01

Marc Audet

46,011
11
63
83

score 0 · Answer 3 · answered Mar 14 '13 at 01:16

I think it would be a good idea to split your ajax request in two.

The first ajax request pulls only the available zip/postal-codes from a table (a new one, only with zip/postal-codes).

The second ajax request fires when the zip/postal-code is completly entered (or when the user does't add new numbers) and returns the city and the country.

The reduced size of a single column, numbers only table could improve your first ajax request.

Nonetheless you have to add a index on your "big" table. Uses phpmyadmin to do so. As a rule of thumb: Index only the column / columns from your where clause (in your case ONLY PostalCode)

score 0 · Answer 4 · answered Mar 14 '13 at 02:18

Check out Adminer. It's very simple to install and can be used to quickly modify the indexes for your tables.

You'll want to create an index on PostalCode and if possible you'll want PostalCode to be your primary key.

If you're still having trouble with long query times, try increasing your Innodb_buffer_pool so that it is large enough to contain the full postalcode table.

score 0 · Accepted Answer · answered Mar 16 '13 at 17:22

0

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 !!!!!!!

answered Mar 16 '13 at 17:22

Daniel Berthiaume

100
1
3
14

How to create an index for a large DB table

5 Answers5