Directory links page very slow loading when calculating

[sammy444]

Hi,

 

I’m not a programmer, but am trying to get an issue solved on my current site development. Currently my programmer has tried a couple solutions and both have issues.

 

This is a classified directory site with approximately 30,000 cities in the database. I’m trying to ONLY show cities that actually have records available in the records database, or that meet the radius settings. These settings are in the admin area where I can set them to 1 to 100 miles. This radius setting allows records to be pulled into cities that don’t actually have a record for that city, but are within the radius set in admin..

 

While this works fine to correctly display city pages, the issue of the state/links pages is that the pages slow down a lot.

So far my programmer has tried 2 ways to achieve this.

 

1. On state links pages where all cities for that state are printed to the page we find that this is awfully slow. For a state like California that has about 1200 cities in our database the script is going 1 by 1 checking each city to see which ones either have records, or meet the radius criteria set. This basically takes forever to load (5 minutes) the links although it does work its just way to slow for any user, or SE’s to follow these links.

 

 

2. This solution was to setup a FLAG (whatever that is) to let the script know before state/links pages are printed witch cities meet the radius settings. This required a messy set of buttons in the admin area where basically to setup radius I have to go state by state (50 buttons) and click to update each state with current radius settings. This also fails often on bigger states like California and basically never applies the settings to all cities in big states, although it does work fine for the smaller states. Why this is done state by state and not with a single button I really don’t know, but I think my programmer mentioned something about not being able to make a mysql query work for all of the states at once.

 

Hope somebody understands this even though its written by a non programmer. Any advice or idea’s to be able to achieve this without slowing down the script would be appreciated. The only other solution is to allow all cities in the database to make a page, and I dont think this is something search engines will want.

 

Thanks.

[Adam]

It's hard to give suggestions for improvement without knowing what's causing the delay. Option 1 sounds like the right approach, but I can't see why it would take five minutes to load. I'm guessing he's first selecting all the rows that are within radius, then performing an additional query for each returned row to find out which have a "record"? That would explain such insane load times, but it's a little concerning that the programmer you sound like you might be paying isn't capable of writing joins.

 

Can you shed any more light on the implementation, or get your programmer to talk to us?