Jump to content

Archived

This topic is now archived and is closed to further replies.

fingerprn

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.