Re: ORDER BY Zip Code Distance

From: Jochen Daum (jochen.daum_at_cans.co.nz)
Date: 02/15/04 

Date: Mon, 16 Feb 2004 08:26:14 +1300

Hi !

On Sat, 14 Feb 2004 06:09:13 GMT, "Xenophobe" <xenophobe@planetx.com>
wrote:

>Jochen,
>
>You're right, "huge" is relative.
>
>The zip code table consists of over 52K records. The company supplier lists
>it's joined with is under 3K.
>
>By "indexing", do you mean MySQL indexing? If so, I've didn't have much luck
>with that. It didn't seem to speed things up although it's quite possible
>the wrong columns were indexed or not enough space was allocated.
>
>Here's the schema for the two tables. Can you recommend which tables to
>index and what sizes to define?
>
>Any suggestions would be appreciated.

I suggest you implement the table schema I suggested in the other
thread, with no minute information and also put an index on
Suppliers.zipcode.

HTH, Jochen

>
>Thanks!
>
>----------------------------------------
>
>TABLE: Locations
>
>LocationID
>ZipCode
>City
>State
>StatusCode
>AreaCode
>TimeZone
>Latitude
>Longitude
>
>TABLE: Suppliers
>
>SupplierID
>SupplierName1
>SupplierName2
>Address1
>Address2
>City
>State
>Zip
>Phone
>
>
>"Jochen Daum" <jochen.daum@cans.co.nz> wrote in message
>news:lbco20pjvn847b6153d9od19vhf1j38ocq@4ax.com...
>> Hi!
>>
>> On Fri, 13 Feb 2004 01:34:00 GMT, "Xenophobe" <xenophobe@planetx.com>
>> wrote:
>>
>> >Doug,
>> >
>> >This is just a quick follow-up. The zip sort by distance works great
>> >except... the performance is pokey for searches of 20+ miles. I could
>simply
>> >sort the results by company name (ditching the beautiful syntax you help
>me
>> >with!), but even this won't save enough cycles for broader searches.
>> >
>> >The options (in my view) are either break out the results in multiple
>pages
>> >or pre-calculate every possible lat and long and store them in the
>database
>> >(creating one huge table!) Can you think of anything that might help?
>>
>> Well, depends what you think is huge. It might still perform very well
>> with proper indexing. How many rows are we talking?
>>
>> Eg. half the world fits in around 4 GB of data for going down to
>> minutes, without even thinking about a good encoding for your
>> latitude/ longitude index. So this would mean around 4 disk accesses,
>> after that you read payload data.
>>
>> Of course I expect you are measuring only one country, so you'll have
>> much less data.
>>
>> HTH, Jochen
>> --
>> Jochen Daum - Cabletalk Group Ltd.
>> PHP DB Edit Toolkit -- PHP scripts for building
>> database editing interfaces.
>> http://sourceforge.net/projects/phpdbedittk/
> 

-- 
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/