Jump to content

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


fingerprn

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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.