Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Re: [pugdog] Using INNER JOIN to duplicate CatLinks

Quote Reply
Re: [pugdog] Using INNER JOIN to duplicate CatLinks In reply to
To reply to some of your suggestions
In Reply To:

You would pick up significant speed by using the pre-generated tables like most of the programs/systems out there offer.[/quote]
How would you know what to "pregenerate"? With 70,000 zipcodes, at distances of say 10,20,50,100 would amount to 240,000 pregenerated tables. Obviously I don't understand the idea Unsure

In Reply To:
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.


RGB World, Inc. - Software & Web Development.
Subject Author Views Date
Thread Using INNER JOIN to duplicate CatLinks rgbworld 2641 Jul 6, 2006, 11:16 AM
Thread Re: [rgbworld] Using INNER JOIN to duplicate CatLinks
pugdog 2527 Jul 20, 2006, 9:44 PM
Post Re: [pugdog] Using INNER JOIN to duplicate CatLinks
rgbworld 2495 Jul 21, 2006, 11:18 AM
Post Re: [pugdog] Using INNER JOIN to duplicate CatLinks
rgbworld 2498 Jul 21, 2006, 11:39 AM