this code is very slow

[spdwrench]

this code loads 10 members thumbnails to be displayed on the home page.... I did not write the original code but I modified it...

 

it seems to take a while to comeback with the results...

 

is there a way to optimize this code.... is something written Poorly??? thanks for any help or comments

 

$personnum = 0;
while($tnct<11)
{
	$search_query = "SELECT p.id, p.gender, f.filename_1 FROM (dt_profile p, dt_photos f, dt_members m, dt_privacy pr) WHERE (p.status='1' AND (pr.featured_yn IS NULL OR pr.featured_yn='') AND pr.member_id=m.id AND p.member_id=m.id AND f.member_id=m.id AND f.filename_1<>'' AND p.gender='$genders[name]') ";

			$search_query .= "ORDER BY RAND()";
	        $search_query .= " LIMIT 1";

	$person[$personnum] = f(q($search_query));

	$personnum++;
	$tnct++;
	if ($genders[name]=="male"){$genders[name]="female";}else{$genders[name]="male";}
}

$curr = 0;

    while($curr < $personnum)
{
    if($person[$curr][id] != "")
    {
    	$current_profile_id = $person[$curr][id];
    		
         if($person[$curr][filename_1] != "") 
         	$profile_image = sysGetThumbnail("photos/{$person[$curr][filename_1]}");
		else 
			$profile_image = '<img src="images/default_photo.gif" width="100" height="100" border="0">';
   
		$top_thumbs .= parseVariables("templates/mainthumbs.html",0);
    }
    $curr++;
}

[icklenewt]

 

Well firstly you don't use the pr table at all. It does have restrictions in the where clause, but from what I can see, it isn't used to limit the data that you actually pull out (i.e data from 'p' and 'f'). Therefore it can be removed:

 

$search_query = "SELECT p.id, p.gender, f.filename_1 
	FROM (dt_profile p, dt_photos f, dt_members m) 
	WHERE (p.status='1' AND p.member_id=m.id AND f.member_id=m.id 
	AND f.filename_1<>'' AND p.gender='$genders[name]') ";

 

 

You also don't really use the members table for much - unless someone can have a profile without being a member? Since the field is called 'member_id' I am going to presume that is not the case. So then we can use:

$search_query = "SELECT p.id, p.gender, f.filename_1 
	FROM (dt_profile p, dt_photos f) 
	WHERE (p.status='1' AND f.member_id=p.member_id 
	AND f.filename_1<>'' AND p.gender='$genders[name]') ";

 

In the above, what I have done is removed the members table, and replaced the 2 member_id checks with:

 

f.member_id=p.member_id

 

This ensures the data is mapped correctly between the two tables, which is what you were using the members table for before.

 

----

Now, you're 'while' loop seems to be trying to get 1 random member, followed by 1 random member of a different gender, followed by 1 random .... etc. This means you are doing the same query 10 times! You can cut this down significantly, which would be much more efficient. Presuming you are aiming at getting 5 random females, and 5 random males. We can do the following:

 

##############################################
# displayPerson
# 
# Displays a profile for the next person in the 
# given array
##############################################
function displayPerson($people, $number)
{
if($person[$curr][id] != "")
{
    	$current_profile_id = $person[$curr][id];	    		
                if($person[$curr][filename_1] != "") 
         	       $profile_image = sysGetThumbnail("photos/{$person[$curr][filename_1]}");
	else 
		$profile_image = '<img src="images/default_photo.gif" 
				  width="100" height="100" border="0">';
   
	$top_thumbs .= parseVariables("templates/mainthumbs.html",0);
}
//reduce the number of this kind of person left to display
$number--;

}

##############################################
# getProfiles
# 
# Obtains the profiles for 5 random people of a set gender 
# into the given array
##############################################
function getProfiles($people, $number, $gender)
{
//Get 5 random people of a set gender
$search_query = "SELECT p.id, p.gender, f.filename_1 
		FROM (dt_profile p, dt_photos f) 
		WHERE (p.status='1' AND f.member_id=p.member_id 
		AND f.filename_1<>'' AND p.gender='$gender') ";

$search_query .= "ORDER BY RAND()";
$search_query .= " LIMIT 5";

//store the people
$result=mysql_query($search_query);
$number=0;
while ($row = mysql_fetch_array ($result)) {
	       $people[$number] = $row;		
	       $number++;
}
}


//Get 5 random males
getProfiles($malepeople,$malenum,"male");
$personnum = personnum+$malenum;
//Get 5 random females
getProfiles($femalepeople,$femalenum,"female");
$personnum = personnum+$femalenum;


//display the profiles - alternating between male and female
while($personnum>0)
{
    //print out a male person
    displayPerson($maleperson,$malenum);
    //print out a female person
    displayPerson($femaleperson,$femalenum);
    //reduce the # of people left to display.
    //personnum now equals how many females and males there are left to display
    $personnum = $malenum+$femalenum;
}

 

I've used some functions so we can use the same code to get 5 males and 5 females, and again the same code snippet when displaying each. So now we have them in separate arrays, but we have significantly reduced the number of queries that have to run - we just do 1 query to get the 5 males, and 1 to get the females.

 

I haven't tested any of this, and I haven't written any php or mysql in a few months so there may be a few mistakes. Just let me know how it goes! The queries may be able to be optimised into 1, just then there could be problems alternating the results of male/female.

 

Hope this helps,

newt