How to optimize you ZIPCodes

I have noticed that there are many duplicate ZIP Code entries in the Dolphin database. in fact there are 354,478 duplicates in th UK zipcodes alone, this takes up space in the database and can slow search by distance.

Here is how I have removed the duplicate entries if anyone is interested.

You will need to run 3 queries on your database but always backup your database before making changes. go to your databse in PHPMyAdmin.

Step 1:

Move the non duplicates (uniques) into a temporary table with this query:

CREATE TABLE ZIPCodes2 AS

SELECT * FROM ZIPCodes WHERE 1 GROUP BY ZIPCode;

Step 2.

We no longer need the table with all the duplicate entries, so drop it! with this query:

DROP TABLE ZIPCodes;

Step 3.

Rename the new_table to the name of the old_table with this query:

RENAME TABLE ZIPCodes2 TO ZIPCodes;

now all duplicate entries are gone. check the amount of lines before and after in the ZIPCodes table, there is a huge difference. also, if you have many country zipcodes installed, you will need to be patient as it can take some time to move the entries from the old table to the new one.

Thats not the right way to do it.

You dropped the indexes in the table and probably made searching worse without them.

It also drops different city names with the same zip in the US list. Might be needed by some.

I want to add here my little notes

1. Zip codes of UK, yes, we have such base, and it possible old. One interesting moment, this country not so big, and have about 2 millions zip codes. .. zipcode for every home? :)

2. Can notice that several places (even in 1 country can have one ZIP)

for example

INSERT INTO `ZIPCodes` VALUES('2540', NULL, 'Jervis Bay', NULL, 'Australia', NULL, 'Australian Capital Territory', 'AC', NULL, NULL, NULL, NULL, NULL, NULL, -35.133, 150.7);
INSERT INTO `ZIPCodes` VALUES('2540', NULL, 'Hmas Creswell', NULL, 'Australia', NULL, 'Australian Capital Territory', 'AC', NULL, NULL, NULL, NULL, NULL, NULL, -34.96, 150.621);
INSERT INTO `ZIPCodes` VALUES('2600', NULL, 'Canberra', NULL, 'Australia', NULL, 'Australian Capital Territory', 'AC', NULL, NULL, NULL, NULL, NULL, NULL, -35.317, 149.144);

zip code 2600 have about 10 places (villages, settlements etc)

.. so you want just delete it?

but they have different placements on map, different coordinates, .. sometimes this difference not small ..

I think that this is common problem of zip searching ..

now we can`t find ideal solution to perform zip search by radius of all possible places (villages) of one ZIP

it can be complex figure of searching ..

and if we will realize such search, it will take much more time for that :)

if  you can help with any LIVE suggestions  - welcome

OK you are probably right there, I only have UK ZIP's installed and it worked fine on them

Andrey, because of the size of the ZIPCodes table in the database especially if using UK or CANADA ZIP's you will get a database error on many servers when searching more than a few miles from a ZIPCode, like this:

Mysql error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay Found error in file /home/dolphinsite/public_html/search_result.php at line 535. Called db_arr function with erroneous argument #0

I have already spoken to my hosting company, Hostmonster.com about this and here is their reply:

Dear Customer,

I would check your select statement to see if you can optimize it so I doesn't load so much data. We cannot change the size of MAX_JOIN_SIZE to a higher number. It will allow for queries that will make the whole server run slow. So optimization is the only route.

Yes, need optimize and rewrite this current system, but before need found good solution .. but not by way of deleting zip codes :)

We will working for it

You have to select the distinct values from that table.