MySQL Forums
Forum List  »  Partitioning

Slowest query is when I click on a category on my site that has alot of products
Posted by: john hango
Date: September 08, 2010 11:33AM

}
$fct=0;
$fid=array();
$name=array();
$pic=array();
$maxbid=array();
$enddate=array();
$currency=array();
$main_region=array();
$country=array();
$zip=array();
while ($catFeatured = mysql_fetch_array($getFeatItems)) {
$fid[$fct]=$catFeatured['id'];
$name[$fct]=$catFeatured['itemname'];
$pic[$fct]=$catFeatured['picpath'];
$maxbid[$fct]=$catFeatured['maxbid'];
$enddate[$fct]=$catFeatured['enddate'];
$currency[$fct]=$catFeatured['currency'];
$main_region[$fct]=$catFeatured['main_region'];
$country[$fct]=$catFeatured['country'];
$zip[$fct]=$catFeatured['zip'];
$fct++;
}} ?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr valign="top">
<td valign="top" width="100%">
<? if ($_REQUEST['show']=="subcats"&&$_REQUEST['parent']>0) $existItems = getSqlNumber("SELECT id FROM yspnash_categories WHERE parent='".$_REQUEST['parent']."'");
if ($_REQUEST['show']=="subcats"&&$existItems>0) include_once("catsubcategories.php");
else include_once("catlistings.php");?>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="1" height="5"></div>
<table width="100%" border="0" cellspacing="0" cellpadding="6" class="border contentfont">
<tr>
<td><?=$lang[nash4655];?><br /><span class="gpost"><?=$lang[nash4656];?>: <?=displaydatetime($setts['page_last_update'],$setts['date_format']);?></span></td>
</tr>
</table>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="1" height="5"></div>
<? include_once("sef_catkw.php");?>
</td>
<? if ($setts['catbox_leftright']=="right") { ?>
<td valign="top"><img src="<?=$path."themes/".$setts['default_theme'];?>/img/pixel.gif" width="5" height="1"></td>
<td class="sbm2">
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="1" height="5"></div>
<? headerTitle(strtoupper("$lang[Cap_categories]"),1);?>
<table <?=($setts['default_theme']!="v52")?"class=\"bordercat contentfont\" cellSpacing=\"1\" cellPadding=\"2\"":"cellSpacing=\"0\" cellPadding=\"0\"";?> width="100%" border="0">
<? if ($setts['showemptycats']==1) $getCats = mysql_query("SELECT SQL_CACHE * FROM yspnash_categories WHERE parent=0 AND hidden=0 AND userid=0 ORDER BY customXcategory,theorder");
else $getCats = mysql_query("SELECT SQL_CACHE * FROM yspnash_categories WHERE parent=0 AND hidden=0 AND userid=0 AND items_counter>0 ORDER BY customXcategory,theorder");
while ($catBrowse=mysql_fetch_array($getCats)) {
if (eregi('Y',$setts['enable_cat_counters'])) {
if ($s_landname!=""&&$s_landname!="n/a"&&$s_landname!="all"&&$setts['enable_cba']==1) {
$someland = " AND country='".$s_landname."'";
if ($setts['enable_start_on_first_bid']==1) $items_counter = getSqlNumber("SELECT id FROM yspnash_auctions WHERE ((closed=1 AND is_start_on_first_bid=1) OR closed=0) AND active=1 AND deleted!=1 AND listin!='store' AND main_category='".$catBrowse['id']."'".$someland."");
else $items_counter = getSqlNumber("SELECT id FROM yspnash_auctions WHERE closed=0 AND active=1 AND deleted!=1 AND listin!='store' AND main_category='".$catBrowse['id']."'".$someland."");
if ($setts['enable_user_website_item_promoter']==1) $items_counterUWIP = getSqlNumber("SELECT id FROM nash_website_promoter_auctions WHERE main_category='".$catBrowse['id']."' AND matching_main_categoryname=1 AND matching_categoryname=1".$someland."");
else $items_counterUWIP = 0;
} else {
$items_counter = $catBrowse['items_counter'];
if ($setts['enable_user_website_item_promoter']==1) $items_counterUWIP = getSqlNumber("SELECT id FROM nash_website_promoter_auctions WHERE main_category='".$catBrowse['id']."' AND matching_main_categoryname=1 AND matching_categoryname=1");
else $items_counterUWIP = 0;
}
$items_counter = $items_counter+$items_counterUWIP;
}
$nbSubCats = getSqlNumber("SELECT id FROM yspnash_categories WHERE parent='".$catBrowse['id']."'");
if ($nbSubCats>0) $catLink=$path.processLink('categories', array('category' => $c_lang[$catBrowse['id']], 'parent' => $catBrowse['id'], 'show' => 'subcats', 'region' => '0', 'preparent' => $catBrowse['id']));
else $catLink=$path.processLink('categories', array('category' => $c_lang[$catBrowse['id']], 'parent' => $catBrowse['id'], 'region' => '0', 'preparent' => $catBrowse['id']));?>
<tr>
<td><a class="ln" href="<?=$catLink;?>" <?=($catBrowse['hover_title']!="")?"title=\"".$catBrowse['hover_title']."\"":"";?>> <?=$c_lang[$catBrowse['id']];?><? if (eregi('Y', $setts['enable_cat_counters'])) echo ($items_counter!=0)?" (<strong>".$items_counter."</strong>)":"";?></a></td>
</tr>
<? }
if ($setts['enable_user_website_item_promoter']==1) $items_counterUWIP_leftovers = getSqlNumber("SELECT id FROM nash_website_promoter_auctions WHERE matching_main_categoryname=0".$someland."");
if ($items_counterUWIP_leftovers>0) { ?>
<tr>
<td><a class="ln" href="auctions.show.php?option=uncategorized"> <?=$lang[nash3917];?><?=(eregi('Y', $setts['enable_cat_counters']))?" (<strong>".$items_counterUWIP_leftovers."</strong>)":"";?></a></td>
</tr>
<? } ?>
<? if ($_REQUEST['show']=="subcats"&&$existItems>0) {} else { ?>
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="bordercat contentfont c2">
<form action="<?=$btn2_link;?>" method="post" name="sellinthiscatform">
<input type="hidden" name="option" id="option" value="sellinthiscat">
<input type="hidden" name="scatid" id="scatid" value="<?=$_REQUEST['parent'];?>">
<? if ($setts['syiprocess_type']==1) { ?>
<input type="hidden" name="sstep" id="sstep"value="step1">
<? } else { ?>
<input type="hidden" name="sstep" id="sstep"value="step1X">
<? } ?>
<tr>
<td align="center" nowrap="nowrap"><input name="llistin" type="radio" id="listin1" value="auction" checked="checked"><label for="listin1"><?=$lang[nash63];?></label><input type="radio" name="llistin" id="listin2" value="store"><label for="listin2"><?=$lang[nash64];?></label><input type="radio" name="llistin" id="listin3" value="both"><label for="listin3"><?=$lang[nash65];?></label></td>
</tr>
<tr>
<td align="center" nowrap="nowrap"><input name="sellinthiscatgo" type="submit" value="<?=$lang[nash66];?>" style="width:160px; height:30px;" onClick='sellinthiscatform.sellinthiscatgo.value="<?=$lang[nash67];?>";return true'></td>
</tr>
</form>
</table>
</td>
</tr>
<? } ?>
</table>
<? if (getSqlNumber("SELECT id FROM yspnash_categories WHERE parent='".$_REQUEST['parent']."'")>0) { ?>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="180" height="5"></div>
<? headerTitle(strtoupper("$lang[nash3607]"),1);?>
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="bordercat contentfont">
<? $getTopCatska = mysql_query("SELECT SQL_CACHE * FROM yspnash_categories WHERE parent='".$_REQUEST['parent']."' ORDER BY items_counter DESC LIMIT 5");
$ika=1;
while ($topCatska = mysql_fetch_array($getTopCatska)) {
$nbSubCatska = getSqlNumber("SELECT id FROM yspnash_categories WHERE parent='".$topCatska['parent']."'");
if ($nbSubCatska>0) $catLink=$path.processLink('categories', array('category' => $c_lang[$topCatska['id']], 'parent' => $topCatska['id'], 'show' => 'subcats', 'regionX' => '0', 'preparent' => $topCatska['id'], 'v' => $_REQUEST['v']));
else $catLink=$path.processLink('categories', array('category' => $c_lang[$topCatska['id']], 'parent' => $topCatska['id'], 'regionX' => '0', 'preparent' => $topCatska['id'], 'v' => $_REQUEST['v']));?>
<tr>
<td><?=$ika;?>.</td>
<td width="100%"><a href="<?=$catLink;?>" <?=($topCatska['hover_title']!="")?"title=\"".$topCatska['hover_title']."\"":"";?>><?=$c_lang[$topCatska['id']];?></a></td>
</tr>
<? $ika++;
} ?>
</table>
<? } ?>
<? $mainCat_primary = getMainCat($_REQUEST['parent']);
$nbStores = getSqlNumber("SELECT id FROM yspnash_users WHERE main_mystorecat='".$mainCat_primary."' AND aboutpage_type=2 AND store_active=1 AND nb_items>0");
if ($nbStores>0) { ?>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="180" height="5"></div>
<? headerTitle(strtoupper("$lang[nash4640]"),1);?>
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="bordercat contentfont">
<? $getTop5Stores = mysql_query("SELECT SQL_CACHE * FROM yspnash_users WHERE main_mystorecat='".$mainCat_primary."' AND aboutpage_type=2 AND store_active=1 AND nb_items>0 ORDER BY RAND() LIMIT 5");
while ($top5Stores = mysql_fetch_array($getTop5Stores)) { ?>
<tr>
<td>
<img src="images/bullet_cma.gif" border="0" align="absmiddle">
<? if ($top5Stores['shop_logo']!="") { ?>
<a href="<?=processLink('shop', array('store' => $top5Stores['store_name'], 'userid' => $top5Stores['id']));?>" onMouseover="ddrivetip('<img src=\'makethumb.php?src=<?=$top5Stores['shop_logo'];?>&w=200&h=200\' border=0>',200);" onMouseout="hideddrivetip();"><strong><?=substr(strip_tags(addSpecialChars($top5Stores['store_name']), '<br>'),0,23)."...";?></strong></a>
<? } else { ?>
<a href="<?=processLink('shop', array('store' => $top5Stores['store_name'], 'userid' => $top5Stores['id']));?>"><strong><?=$top5Stores['store_name'];?></strong></a>
<? } ?>
</td>
</tr>
<? } ?>
</table>
<? } ?>
<? $nbPopularSearches = getSqlNumber("SELECT id FROM nash_searchitemkeywords WHERE main_category='".$mainCat_primary."' AND main_category>0");
if ($nbPopularSearches>0) { ?>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="180" height="5"></div>
<? headerTitle(strtoupper("$lang[nash4641]"),1);?>
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="bordercat contentfont">
<? $getPopularSearches = mysql_query("SELECT SQL_CACHE * FROM nash_searchitemkeywords WHERE main_category='".$mainCat_primary."' AND main_category>0 ORDER BY RAND() LIMIT 5");
$ika=1;
while ($popularSearches = mysql_fetch_array($getPopularSearches)) { ?>
<tr>
<td><?=$ika;?>.</td>
<td width="100%"><a href="auctionsearch.php?basicsearch=<?=$popularSearches['keyword'];?>" onMouseover="ddrivetip('<?=$lang[nash100];?>: <?=date($setts['date_format'],$popularSearches['date']);?><br>IP: <?=$popularSearches['ip'];?>',200);" onMouseout="hideddrivetip();"><?=substr(strip_tags(addSpecialChars($popularSearches['keyword']), '<br>'),0,23)."...";?></a></td>
</tr>
<? $ika++;
} ?>
</table>
<? } ?>
<? if ($nbFeatItems>0) { ?>
<div><img src="<?=$path;?>themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="180" height="5"></div>
<? headerTitle(strtoupper("$lang[nash4524]"),1);?>
<table width="100%" border="0" cellspacing="0" cellpadding="3" class="bordercat contentfont">
<? $numb=0;
for ($i=0;$i<$j;$i++) { ?>
<tr>
<td>
<img src="images/bullet_cma.gif" border="0" align="absmiddle">
<? if ($pic[$numb]!="") { ?>
<a href="<?=processLink('auctiondetails', array('itemname' => $name[$numb], 'id' => $fid[$numb]));?>" onMouseover="ddrivetip('<img src=\'makethumb.php?src=<?=$pic[$numb];?>&w=200&h=200\' border=0>',200);" onMouseout="hideddrivetip();"><strong><?=substr(strip_tags(addSpecialChars($name[$numb]), '<br>'),0,23)."...";?></strong></a>
<? } else { ?>
<a href="<?=processLink('auctiondetails', array('itemname' => $name[$numb], 'id' => $fid[$numb]));?>"><strong><?=$name[$numb];?></strong></a>
<? } ?>
</td>
</tr>
<? $numb++;
} ?>
</table>
<? }
$thiszoneid = 6;
include("getuserbanner.php");?>
<div><img src="themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="180" height="1"></div>
</td>
<? } ?>
</tr>
</table>
<div><img src="themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="1" height="5"></div>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center" valign="middle">
<? $nbAds = 0;
if (eregi("classified.details.php",$_SERVER['PHP_SELF'])) $bannerlocation = " AND (bannerlocation='001' OR bannerlocation='011' OR bannerlocation='101' OR bannerlocation='111')";
else if (eregi("wanted.details.php",$_SERVER['PHP_SELF'])) $bannerlocation = " AND (bannerlocation='010' OR bannerlocation='011' OR bannerlocation='110' OR bannerlocation='111')";
else if (eregi("auctiondetails.php",$_SERVER['PHP_SELF'])) $bannerlocation = " AND (bannerlocation='100' OR bannerlocation='110' OR bannerlocation='101' OR bannerlocation='111')";
else $bannerlocation = " AND bannerlocation='111'";
if (eregi("categories.php",$_SERVER['PHP_SELF'])) {
$catName = getSqlField("SELECT name FROM yspnash_categories WHERE id='".$_REQUEST['parent']."'","name");
$getAdvert = mysql_query("SELECT SQL_CACHE * FROM yspnash_adverts WHERE cat_filter LIKE '%".$catName."%' AND (views_p=0 OR views_p>=views) AND (clicks_p=0 OR clicks_p>=clicks) AND islink_partner='0' AND hidden='0' AND bannerspot='Bottom_of_Screen'".$bannerlocation." ORDER BY RAND() LIMIT 0,1") or die(mysql_error());
$nbAds = mysql_num_rows($getAdvert);
} else if (eregi("auctiondetails.php",$_SERVER['PHP_SELF'])) {
$advertId=0;
$allAdverts = mysql_query("SELECT SQL_CACHE * FROM yspnash_adverts WHERE (views_p=0 OR views_p>=views) AND (clicks_p=0 OR clicks_p>=clicks) AND keyword_filter!='' AND islink_partner='0' AND hidden='0' AND bannerspot='Bottom_of_Screen'".$bannerlocation." ORDER BY RAND()");
while ($advertsQuery = mysql_fetch_array($allAdverts)) {
$isAdvert = getSqlNumber("SELECT id FROM yspnash_auctions WHERE keywords LIKE '%".$advertsQuery['keyword_filter']."%' AND id='".$_GET['id']."'");
if ($isAdvert>0) $advertId = $advertsQuery['id'];
}
$getAdvert = mysql_query("SELECT SQL_CACHE * FROM yspnash_adverts WHERE id='".$advertId."' AND hidden='0'");
$nbAds = mysql_num_rows($getAdvert);
}
if ($nbAds==0) $getAdvert=mysql_query("SELECT SQL_CACHE * FROM yspnash_adverts WHERE cat_filter='' AND keyword_filter='' AND (views_p=0 OR views_p>=views) AND (clicks_p=0 OR clicks_p>=clicks) AND islink_partner='0' AND hidden='0' AND bannerspot='Bottom_of_Screen'".$bannerlocation." ORDER BY RAND() LIMIT 0,1");
while ($bannerDetails = mysql_fetch_array($getAdvert)) {
$addView = mysql_query ("UPDATE yspnash_adverts SET views=views+1 WHERE id='".$bannerDetails['id']."'");
if ($bannerDetails['type']=="custom") echo "<a href=\"click.php?refid=".$bannerDetails['id']."\" target=\"new\"><img src=\"".$bannerDetails['imgpath']."\" title=\"".$bannerDetails['alttext']."\" border=\"0\"><br>".$bannerDetails['textunder']."</a>";
else if ($bannerDetails['type']=="code") echo addSpecialChars($bannerDetails['banner_code']);
}
mysql_free_result($getAdvert);?>
</td>
</tr>
</table>
<div><img src="themes/<?=$setts['default_theme'];?>/img/pixel.gif" width="1" height="5"></div>
<? include_once("themes/".$setts['default_theme']."/footer.php");?>

Options: ReplyQuote


Subject
Views
Written By
Posted
1470
September 06, 2010 11:38PM
1955
September 06, 2010 11:39PM
Slowest query is when I click on a category on my site that has alot of products
3105
September 08, 2010 11:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.