How can I make this code only load the first 30 rows or run faster on loading?

[jknelsonjk]

When I run this code it takes longer then I want to load the search results. I thought by only loading the first 30 rows it catches it would speed things up. Any ideas? I changed the $db to be 30, but only my first page painted and it was not much faster....

 

if(!empty($keyword) && $keyword!='Zip Code')	
$sql="SELECT profiles.*,zipCode FROM profiles LEFT JOIN area_served  ON area_served.profileId = profiles.profileId WHERE profileType!='HO' AND 

profiles.status='Y' $qrystr GROUP BY profiles.profileId $orderby CASE zipCode  $zips_order_str , companyName asc ";
else
$sql="SELECT * FROM profiles LEFT JOIN area_served  ON area_served.profileId = profiles.profileId WHERE profileType!='HO' and status='Y' $qrystr 

GROUP BY profiles.profileId $orderby ";

//echo  $sql;

$db->query($sql);
$numRows=$db->numRows();
$page->set_page_data($_SERVER[php_SELF],$numRows,$listing,10,true,false,true);
$page->set_qry_string("searchFor=$searchFor&cat=$cat&state=$state&keyword=$keyword&distance=$distance&sorting=$sorting&listing=$listing"); 
$res=$db->query($page->get_limit_query($sql));
$smarty->assign('numResult',$numRows); /* show pagination */



while($row=$db->fetchAssoc($res))
{	
if($row['thumbnailPhoto']=='personalPhoto' || $row['thumbnailPhoto']==''){
	if(!empty($row['personalPhoto'])){
	$personalPhoto="profiles/".$row['personalPhoto'];
	$rimg->setImage($personalPhoto);
	$row['personalPhoto']=$rimg->resize(75,75,substr($personalPhoto,0,strpos($personalPhoto,"."))."_search");
	}else{
	$row['personalPhoto']="images/my-photo.gif";
	}
}else{
	if(!empty($row['logo'])){
	$personalPhoto="profiles/".$row['logo'];
	$rimg->setImage($personalPhoto);
	$row['personalPhoto']=$rimg->resize(75,75,substr($personalPhoto,0,strpos($personalPhoto,"."))."_search");
	}else{
	$row['personalPhoto']="images/my-photo.gif";
	}
}
if(count($cat>0)){
$row['cat']=@implode(", ",$cat);
}

if(strlen($row['companyInfo'])>90){
	$concat="...";
}
else{
		$concat="";
}

[requinix]

There's not nearly enough information to know why it seems slow to you. What's $db? What's $page? Is that stuff with $rimg actually resizing the image every single time?

[mikosiko]

and expanding  requinix questions:

 

- Which is the size of tables profiles and area_served ? (row number)

- Are they indexed?... which columns?

[jknelsonjk]

The Profile table has ~240k rows. The Area table has only 41

[Maq]

In the future, please use

tags around your code.

[mikosiko]

look into the resizing of each record issue then as per requinix suggestion

[The Little Guy]

if you have phpmyadmin, or mysql in a terminal, run the query using "explain" before each select. Example:

 

explain select * from my_table where user_name='Mickey Mouse';

 

The columns you want to look at is:

possible_keys and key

 

if key returns null, you probably want to add some indexes to your table.

 

so, I might index your first query like so:

 

ALTER TABLE  profiles ADD INDEX (profileType, status);