Page 1 of 1

optimizing code to generate categories list faster..

Posted: Mon Feb 15, 2010 3:43 pm
by Bleeper
Hello!

I would truly appreciate if someone could help me optimizing this code. It's basically a photo gallery site where the photos are categorized by subject. I want to display the list of categories and the amount of pictures that each category has. I have over 150 categories with over 1000 photos.

It's working so far but it takes like 20 sec to get the results, any idea why and how it could be solved ?

Cheers!
B.

Code: Select all

       
 
<?
 
    $sql2 = mysql_query("SELECT a.title,a.id,a.active,count(b.id) as 'amount', b.gallery_id FROM photo_galleries a LEFT JOIN photo_package b on a.id = b.gallery_id where a.active = '1' group by title order by amount");
 
        $number = mysql_num_rows($sql2);
    
        echo "<div style=\"margin-bottom:20px;\">There are <b> ".$number." </b> Categories</div>";
 
        $data = array();
        
        if ($order_by == "alphabet") {
        
        while ($row = mysql_fetch_array($sql2))
        {
        
        $firstletter = strtoupper(substr($row['title'],0,1));
        
        if ($firstletter == "Ö") {
            $firstletter = "OE";
        }
    
        if ($firstletter == "Ü") {
            $firstletter = "UE";
        }
    
        if ($firstletter == "Ä") {
            $firstletter = "AE";
        }
        
        $count = mysql_fetch_array(mysql_query("Select count(*) from `photo_package` where `gallery_id` = ".$row['id']." or other_galleries like '%".$row['id']."%'"));
        
        $data[$firstletter][] = array(0 => $row['title'], 1 => $row['id'], 2 => $count[0]);
        
        }
        
        setlocale (LC_ALL, 'de_DE');
                
        if ($sort_order == "ascending") {
        ksort($data, SORT_LOCALE_STRING);
        }
        else {
        krsort($data, SORT_LOCALE_STRING);
        }
            
        $number = round(count($data) / 3);  
            
        ?>
 
        <div>
        <table style="border:none; width:100%;">
        <tr>
        <td valign="top">
       
        
       
        <?
        
        $counter = 1;
        
        foreach ($data as $key => $val){
            
            $a = $key;
            
            if ($a == "OE") {
            $a = "Ö";
            }
            
            if ($a == "AE") {
            $a = "Ä";
            }
            
            if ($a == "UE") {
            $a = "Ü";
            }
        
        echo "<div class=\"key_1\">".$a."</div><div class=\"key_2\">";
        
            foreach ($val as $key2 => $val2) {
            echo "<a href=\"gallery.php?gid=".$val2[1]."\">".$val2[0]."</a><span style=\"color:#666666;\"> (".$val2[2].")</span><br />";
            }
        
        echo "</div>";
        
                    if ($counter > $number){
            ?>
            </td>
            <td valign="top">
            <?
            $counter = 0;
            }
        
        $counter = $counter + 1;
        }
            
        }
        else {
        
        $sql2 = mysql_query("SELECT * FROM photo_galleries where active = '1'");
        while ($row = mysql_fetch_array($sql2))
        {
        
        
        $sql3 = mysql_query("Select * from photo_package where gallery_id = '".$row[id]."' or other_galleries like '%".$row[id]."%'");
        $amount = mysql_num_rows($sql3);
            
        $data[] = array(0 => $amount, 1 => $row['title'], 2 => $row['id']);
        
        }
        
        if ($sort_order == "descending") {
        rsort($data);
        } else {
        sort($data);
        }
        
        
        $number = round(count($data) / 3);
        ?>
        
        <table style="border:none; width:100%;">
        <tr>
        <td valign="top">
        
        <?
        
        foreach ($data as $key => $val){
        
            
            echo "<div class=\"key_2\" style=\"margin:0px;\"><a href=\"gallery.php?gid=".$val[2]."\">".$val[1]."</a><span style=\"color:#666666;\"> (".$val[0].")</span></div>";
            
        if ($counter > $number){
            ?>
            </td>
            <td valign="top">
            <?
            $counter = 0;
            }
        
        $counter = $counter + 1;
        
        }
                    
        }
        ?> 
 

Re: optimizing code to generate categories list faster..

Posted: Wed Feb 17, 2010 2:03 pm
by BDKR
Based on the way all that markup and logic is mixed together in there, I'm going to say you don't have a ton of experience (unless this is someone else's code you've inherited). That said, I'm hoping you get what I have to say here.

I haven't really looked too much at your code BTW. I'm just staying at a higher level based on your description of the problem.

What I like to do in cases where there are large or convoluted data sets that take some overhead and time to generate, is cache them. Sometimes, that can be as simple as writing it out to file and reading it back in. It's bound to be a damn site faster then running the logic over and over again to create the data structure.

Additionally, if some change is made that forces this data structure to be rebuilt, you could make it a stand alone process that runs in the back end. As an example, if someone adds a new category and 756 pics, call the process in the backend and have it replace the old data structure with the new. This way, that 20 second wait is never felt by the user.

Another option is to NOT build the entire data structure at each view, but to be lazy about it. The approach is called "Lazy Loading".

http://en.wikipedia.org/wiki/Lazy_loading

In other words, only build the data structure at it's highest level (Perhaps only one level of categories), then build the sub categories on request.

Going on, do you know where most of the time taken to run this code is being spent? The database? The code itself? Find this out for sure as it will give you a big indicator as to where to spend your attention.

Cheers

Re: optimizing code to generate categories list faster..

Posted: Mon Feb 22, 2010 5:23 pm
by josh
Can you paste the output of EXPLAIN? http://forums.spry.com/howtos/1345-usin ... mmand.html