MySQL SELECT statement creating slow pages, any way to optimize?

[SpankMarvin]

Hi there!

 

I have a sponsor listing in which I have to run a query to produce a list of categroies into which sponsors might fall. This is outlined as follows:

 

- User selects to browse all sponsors by category

- Categories fall under main categories, sponsor can be linked to one or more (or none) of the categories but not to a main category

- Sponsors can have activation status for non-admin views (they can be active and visible to the public in results, or non-active, and invisible)

- Geographical zones are also specified, which, like sponsors, can be active or non-active

 

The results themselves (e.g. sponsor names) are not important for this particular listing. All that's important is whether or not the category should be listed at all, which it shouldn't be if there are no relevant sponsors to display when clicking through.

 

I have a query, as shown below, within a function that references a category array for each main category (so this function is triggered for each main cat). For each query, I have to ensure that the category is only displayed if AT LEAST ONE sponsor is tied in to that category, AND that sponsor is active, AND that sponsor belongs to a geographical zone that is also active. Before, I had a query within a foreach for every category, which seemed wasteful. However, I was able to limit each of those queries to one row returned. It was, however, slow. In this modified version I am making a query for each main category, but each query is longer as a result. The result is still, at times, very slow to come through.

 

Here is my function:

 

function display_valid_sponsors($cat_array){
if(!is_array($cat_array)){
echo("<div class=\"subcats\"><ul><li>No categories available</li></ul></div>");
return;
}
echo("<div class=\"subcats\"><ul>");

	$conn = db_connect();
	$query = "select categories.catid, catname from categories, linksponsorcats, sponsors, zones, linksponsorzones where";
	$count = 1;
	foreach($cat_array as $key){
		$catid = $key['catid'];
		$catname = $key['catname'];
		if($count == 1){
			$query .= " (linksponsorcats.catid='".$catid."' and linksponsorcats.catid=categories.catid and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' and sponsors.sponsorcode=linksponsorzones.sponsorcode and zones.zoneactivestatus='active')";
		} else {
			$query .= " or (linksponsorcats.catid='".$catid."' and linksponsorcats.catid=categories.catid and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' and sponsors.sponsorcode=linksponsorzones.sponsorcode and zones.zoneactivestatus='active')";
		}
		$count ++;
	}
	$query .= " group by categories.catid order by categories.catname";
	$result = $conn->query($query);

	if ($result->num_rows > 0){
		$result = db_result_to_array($result);
		foreach($result as $key){

			echo("<li><a href=\"list-sponsors.php?zip=".$zip."&catid=".$key['catid']."\">".$key['catname']."</a>");
			if (isset($_SESSION['valid_admin'])) { // User is admin; add editing option for the category
				echo(" | <a href=\"".$URL_ROOT."/admin/edit-category.php?catid=".$key['catid']."\">Edit this category</a>");
			}
		}
	echo("</li>");
	} else {
	echo("<li>No categories available</li>");
}

echo("</ul></div>");
}

 

I am convinced that I could be rewriting this query more efficiently, but my limits of SQL are hitting me hard. Can anyone give me some tips on how to shape it up? I'd really appreciate any advice.

 

Thank you

 

John

[SpankMarvin]

Hi all

 

I've made the fairly obvious step of taking the AND clauses outside of the OR clauses, and putting them only once at the end of the query, which obviously cuts down on the amount of querying significantly.

 

New code:

 

function display_valid_sponsors($cat_array){
if(!is_array($cat_array)){
echo("<div class=\"subcats\"><ul><li>No categories available</li></ul></div>");
return;
}
echo("<div class=\"subcats\"><ul>");

	$conn = db_connect();
	$query = "select categories.catid, catname from categories, linksponsorcats, sponsors, zones, linksponsorzones where";
	$count = 1;
	foreach($cat_array as $key){
		$catid = $key['catid'];
		$catname = $key['catname'];
		if($count == 1){
			$query .= "(linksponsorcats.catid='".$catid."'";
		} else {
			$query .= " or linksponsorcats.catid='".$catid."'";
		}
		$count ++;
	}
	$query .= ") and linksponsorcats.catid=categories.catid and linksponsorcats.sponsorcode=sponsors.sponsorcode and sponsors.sponsoractivestatus='active' and sponsors.sponsorcode=linksponsorzones.sponsorcode and zones.zoneactivestatus='active' group by categories.catid order by categories.catname";
	$result = $conn->query($query);

	if ($result->num_rows > 0){
		$result = db_result_to_array($result);
		foreach($result as $key){
			echo("<li><a href=\"list-sponsors.php?zip=".$zip."&catid=".$key['catid']."\">".$key['catname']."</a>");
			if (isset($_SESSION['valid_admin'])) { // User is admin; add editing option for the category
				echo(" | <a href=\"".$URL_ROOT."/admin/edit-category.php?catid=".$key['catid']."\">Edit this category</a>");
			}
		}
	echo("</li>");
	} else {
	echo("<li>No categories available</li>");
}

echo("</ul></div>");
}

 

I would still be interested if anyone could weigh in on any ways I could optimize further, though.

 

Thanks

 

J

[fenway]

I can't wade through code.

 

Get the raw SQL query, run EXPLAIN on it, and post your results here.