current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm getting terribly slow results from the following code. I've been trying to troubleshoot it for hours but with no results. I've included only the relevant code.

Edit:

I am trying to select an id from a database and then use that id to get all the images associated with it. I am then narrowing that group of images down to one. Once I have that image I am resizing it through an external file.

I've tried removing various parts of the code to identify the problem and it seems as if the slow down is being caused by the second query but I am not sure why. Thanks for your help.

$getworks = mysql_query ("SELECT a_id from artists where display_works = '1' and active = '1' order by project_year desc, fullname desc");

while ($getworksrow = mysql_fetch_assoc($getworks)){
            $totalimages=1;
            $addstyle = "";
            $id = $getworksrow["a_id"];
            $getimages = mysql_query ("SELECT a_id, image_id from images where a_id = '". $id ."' order by position asc LIMIT 1");
            $getimagesrow = mysql_fetch_assoc($getimages);

            foreach ($getimagesrow as $getimagesrows){
                extract($getimagesrow);

                if($totalimages > 1){ $addstyle = 'style="display:none;"'; }
                else {
                $myimagename = "http://artist.com/$a_id/images/$image_id" .  "_large.jpg";
                list($width, $height, $type, $attr) = getimagesize("$myimagename");
                $myimagename = "http://artist.com/artists/resize.php/$a_id/images/$image_id" . "_large.jpg?resize(157x2000)";

                if($getworksrows["layout"] == "vert"){$pl = "_vertical";}else if($getworksrows["layout"] == "website"){$pl = "-s";}else if($getworksrows["layout"] == "video"){$pl = "_video";}else{$pl = "_horizontal";}
                echo "<li class='thumbnail_container' $addstyle> <a class='thumbnail' href=\"../works$pl.php?a_id=" . $getworksrows["a_id"] . "\"><span><img src=\"$myimagename\" /></span>\n</a></li>\n";
                }

                $totalimages++;
            }

}
share|improve this question
1  
It's most likely the getimagesize on a remote URL that is slow. There may be no way to speed that up. Are you caching the results somewhere? –  Pekka 웃 Feb 17 '12 at 9:32
    
Please be more specific about your question, what you are trying to etc. –  jogesh_pi Feb 17 '12 at 9:32
1  
Nested SQL queries are always a big performance overhead, in this case you're issuing 1 + 1 per image queries, when you only need to execute 1 (if you learn about JOINs) –  Mark Baker Feb 17 '12 at 9:42
    
Could you clarify on this? When you say nested AQL I assume you mean my second query which I've identified as the problem. How would I use a JOIN to improve performance? –  Nicholas Arehart Feb 17 '12 at 9:45
    
imagine this, for every item you first get (say 1000), you are executing secondary queries per item, another 1000 queries! that's the overhead! as compared to a JOIN, you only have to query once to get both tables. –  Joseph the Dreamer Feb 17 '12 at 9:47

1 Answer 1

up vote 2 down vote accepted

It's a a big performance overhead to execute queries like this specially when parent query have large no. of records.

You should use join artists table with images table and get all data by single query.

Later on make 2D array of per artists and images. and loop according to 2D array to display data

Below is join query you should use: 

SELECT * from artists as art 
left join images as img  on art.a_id=img.a_id 
where display_works = '1' and active = '1' 
order by project_year desc, fullname desc

In While make data array:

   while ($getworksrow = mysql_fetch_object($getworks)){

    $data['a_id']['img_id']=$getworksrow->image;  //Make 2D array
   ........
   ........

    }

looping and display data :

foreach($data as $id=>$images)
{
   foreach($images as $val){
     // Do your stuff for displaying data
   }

}

So please do required changes.

share|improve this answer
    
i suppose this will help if you have "combinations" in effect of the joins stackoverflow.com/q/9156109/575527 –  Joseph the Dreamer Feb 17 '12 at 9:50
    
Can you more thoroughly explain this? I'm not quite sure I understand. –  Nicholas Arehart Feb 17 '12 at 9:56
    
I don't understand line following the while statement. Where did [$artist_id], [$img_is] and $getworksrow->image get defined? –  Nicholas Arehart Feb 17 '12 at 10:32
    
that is not actual code .It's just format how to make 2D array. Just do something like this with your actual field names. It's not possible to paste whole code here –  user319198 Feb 17 '12 at 10:39
    
Yeah, okay but I don't think your join query is working. I can't get it to return any data. –  Nicholas Arehart Feb 17 '12 at 10:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.