Jump to content


Photo

There's got to be a better way to do this...


  • Please log in to reply
2 replies to this topic

#1 fingerprn

fingerprn
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 17 August 2006 - 07:39 PM

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);
?>



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 17 August 2006 - 08:13 PM

This bit worries me a little

FROM events, adverts

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 fingerprn

fingerprn
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 17 August 2006 - 10:44 PM

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...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users