fingerprn Posted August 17, 2006 Share Posted August 17, 2006 My home page loads extremely slowly and I've finally isolated the problem. Below is a section of script from my home page on a dating site. First of all, it does work without error. However, it is EXTREMELY SLOW and I'm assuming there must be a better way to approach this. The script displays the five closest events (parties, etc.) to the end user, sorted by distance from that user (using zip codes). The rest of the script is really just to display the location. With every event that's added to the database, the home page gains a second of load time. When 5 events are added, it takes 7 seconds for my home page to load! I know the problem has to do with calculating distance because I've got a travel section on the page that is exactly the same except it does not have the zip code snippet and does not seem to slow down the page when 5 travel events are added. Any help or guidance would be appreciated. ???John<?php$query="SELECT ev_eventid, abs(zip1.zip_latitude - zip2.zip_latitude) + abs(zip1.zip_longitude - zip2.zip_longitude) as mindist FROM events, adverts LEFT JOIN zipcodes zip1 ON (zip1.zip_zipcode = ev_zipcode) LEFT JOIN zipcodes zip2 ON (zip2.zip_zipcode = adv_zipcode) WHERE ev_approved = '1' AND adv_userid = '$Sess_UserId' GROUP BY ev_eventid ORDER by mindist asc LIMIT 5";//echo $query; $res=mysql_query($query) or die(mysql_error()); while ($event = mysql_fetch_object($res)){ $query="SELECT *, unix_timestamp(ev_schedule) event_time FROM events LEFT JOIN geo_country ON (gcn_countryid = ev_country) LEFT JOIN geo_state ON (gst_stateid = ev_state) LEFT JOIN geo_city ON (gct_cityid = ev_city) WHERE ev_approved = '1' AND ev_eventid = '$event->ev_eventid' "; //echo $query; $retval=mysql_query($query,$link) or die(mysql_error()); $row = mysql_fetch_object($retval); $address = array(); if ($row->gct_name) $address[] = $row->gct_name; if ($row->gst_name) $address[] = $row->gst_name; if ($row->gcn_name) $address[] = $row->gcn_name; $address = join(', ',$address);?> Quote Link to comment Share on other sites More sharing options...
Barand Posted August 17, 2006 Share Posted August 17, 2006 This bit worries me a little[code]FROM events, adverts[/code]as I can't see the join criteria between these 2 tables.If you "select from a,b" without specifying the fields to join on, then you get a cartesian join - ie every record in a is joined to every record in b. So if you have 100 records in each the join results in 10,000 rows. Quote Link to comment Share on other sites More sharing options...
fingerprn Posted August 17, 2006 Author Share Posted August 17, 2006 Nevermind, I figured it out. My original developers never indexed the dang zip codes in MySQL!!! Now it's lightening fast. Thanks Barand. If it slows down again after my membership grows, I'll take a closer look a your suggestion. For now, if it ain't broke... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.