I've migrated my version to using one table, but adding a "distance" field to the record, so selects are on both zip code and distance.[/quote]I've gone back and forth on the issue a dozen times, and IMHO, if you are duplicating information from one table to another (which I was doing), then it should be re-thought. I now use just a ZipCodes table and the Links table, and results are joined on the only common field, which is the ZipCode.
In Reply To:
If you try to recalculate each and every record each and every time, you have a far, far greater system load than just doing a simple select:
select * where ZIP = zip and DISTANCE = distance
You get ONE record returned from an indexed database, with a field "INCLUDED_ZIPS" or whatever.
In that record is a data-base normalization defying list of zipcodes (your list separator can be anything you want, a space is fine, but I think the ', ' turns it into an IN list automatically.
Then, you can just search on all records whose ZIP is IN (INCLUDED_ZIPS)[/quote]Well, of course if every Zipcode knew how far it was from every other zipcode a single select
like your example would be easy. How do you plan to update the list of
INCLUDED_ZIPS associated with
each zipcode
In Reply To:
According to the MySQL manual, IN searches are blindingly fast.[/quote]IN is fast, but not when comparing say 1000 zipcodes against 150,000 records each individually
In Reply To:
I think you can even make this a single query, via SQL, but maybe not in the GT:: SQL engine using the most current MySQL, but separate searches are not horribly penalizing.[/quote]I get my results from a single query by JOINing 2 tables. I expect that is the best way to
do it as there is no redundent data in either table.
In Reply To:
You calculate once, then search many times. You sacrifice a bit of disk/database space for the major per-search overhead. Unless you have a majorly overloaded system, you wouldn't need to cache results, though if you keep tabs on what searches are being done most often, and it's something you can cache..... though I'm not sure if in most situations that would actually end up saving anything - especially running GLinks under mod_perl[/quote]
I think that is what I am doing now, I will have to benchmark it under mod-perl to see if it is faster.
Thanks again for your discussion of the matter.
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com 
