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

Re: [rgbworld] Using INNER JOIN to duplicate CatLinks

Quote Reply
Re: [rgbworld] Using INNER JOIN to duplicate CatLinks In reply to
You would pick up significant speed by using the pre-generated tables like most of the programs/systems out there offer.

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.

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)

According to the MySQL manual, IN searches are blindingly fast.

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.

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


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
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 2528 Jul 20, 2006, 9:44 PM
Post Re: [pugdog] Using INNER JOIN to duplicate CatLinks
rgbworld 2496 Jul 21, 2006, 11:18 AM
Post Re: [pugdog] Using INNER JOIN to duplicate CatLinks
rgbworld 2498 Jul 21, 2006, 11:39 AM