[SOLVED] "Loading.. Please Wait" Can't figure it out..

[scottrohe]

Hey guys,
I have a large database of every state/county/city etc that I have a function accessing and randomizing all of the counties per state and pulling just 3.. running 50x; very long load process.. So, I've been messing around trying to figure out how to make either a popup or a div come up saying "Loading.. Please Wait". Nothing seems to work; popups come up AFTER it is done, even if I put an "onClick" on the link that takes you to the page, although that wouldn't fix the problem as some people will be comming straight from search engines.. Pretty much the same thing with java+divs.

Sorry if I posted this in the wrong forum.

Here is my function that I use to randomize the counties..

[code=php:0]
function randomizeCounties($fState){
$sql2 = mysql_query("SELECT * FROM ZIPCodes WHERE StateName='$fState' GROUP BY 'CountyName' ORDER BY RAND() LIMIT 3");
while ($r2=mysql_fetch_array ($sql2))
{   
    $countyName=$r2["CountyName"];
$i ++;
if($i <= "2"){ 
echo "<a href='?p=county-local-insurance-agent-directory&state=".$fState."&county=".$countyName."'>".$countyName."</a>, ";
} else {
echo "<a href='?p=county-local-insurance-agent-directory&state=".$fState."&county=".$countyName."'>".$countyName."</a> ";
}
}
}
[/code]

Any ideas on howto either a) lessen load time to about 2 seconds, rather than 10...
or [b]mainly[/b] b) show a "Loading.. Please Wait" message..

Any help is much appreciated, thanks for your time.

[btherl]

[code=php:0]order by rand()[/code] will be quite slow, unless mysql has special code to optimize it.

First, do you have an index on StateName?  If not, adding that will be a big win for performance.

If that's not good enough, then you could try pre-generating the data grouped by CountyName for each state.  Again that will save you some time.

Then to avoid the time spent ordering by rand(), you can assign consecutive id numbers to each state's data.  Eg, state AZ will be numbered from 1 to n, state TX will be numbered from 1 to n, and so on.  Then you can generate the random numbers yourself, and look them up using an index on State and id.  That'll be ultra-fast.

[artacus]

ORDER BY RAND() requires every entry that qualifies to be fetched. So reducing the number of entries returned (using where) will help. But I'm thinking that you'd have maybe 50-60 counties per state right? If that is slow, that tells me that you don't have your indexes right. Pulling 3 entries from 60 or so should be absolutely instantaneous.

[quote]First, do you have an index on StateName?  If not, adding that will be a big win for performance.[/quote]

Ok, having an index on stateName is an absolute MUST, not an option. CountyName would also be highly recommended. You've got to look at what you are doing with this table. Its not like cities, counties or states are being added on a regular basis. So you don't have write performance to worry about your indexes slowing you down.

Add indexes on these two columns and change it from being 1 query being run 50x to a subquery and query that is run 1x and you'll have your results in under 2 seconds.

[scottrohe]

Wow, thanks guys!

I didn't create the database, didn't even know that indexing the states like that would make that big of a difference in the first place though haha; but that did it. The page now loads in around a second rather than 10+

[artacus]

Yep, you didn't say if you added the CountyName index or not, but if you need to group by countyName I'd definately add that one as well.