Most Complex Query Ever? Haha...

[drath]

Bare with me here, it's going to take some explaining. I am using an INNER JOIN for two separate tables on a single value, but want to to do some counting in between then based on a grouping of a value. I have a working set of nested queries right now, but want to see if I can do it in a single query to improve performance (as including the nesting queries adds a full second on a small set). I will present my working query right now (tried to strip irrelevant items):

 

$ID = 1;
$sql = "SELECT iv.ID, iv.item_id, iv.user_id, i.ID, i.gamename FROM inventory iv INNER JOIN items i ON iv.item_id = i.ID WHERE iv.user_id = ".$ID."";
$result = mysql_query($sql) or die(sql_error($sql));
while($row = mysql_fetch_array($result)) {
	$gamename = $row['gamename'];
	//First Nested
	$sql2 = "SELECT ID, gamename FROM items WHERE gamename = '".$gamename."'";
	$result2 = mysql_query($sql2) or die(sql_error($sql2));
	$setcount = 0;
	while ($row2 = mysql_fetch_array($result2)) {
		$itemid = $row2['ID'];
		//Second Nested
		$sql3 = "SELECT DISTINCTROW item_id, user_id, auctioned FROM inventory WHERE user_id = '".$ID."' AND item_id = ".$itemid."";
		$result3 = mysql_query($sql3) or die(sql_error($sql3));
		while ($row3 = mysql_fetch_array($result3)) {
			$setcount = $setcount + 1;
		}
		$haveset = $setcount;
	}
	$set = mysql_num_rows($result2);
}
echo "User has $haveset out of $setcount items.";

 

Basically I am loading all the items a user has in the first query. The first nested query is in the case to get the "gamename" from the main query, then the second nested query is to see how many items that user has from that gamename out of the full set that is actually available. Again, I have stripped a lot of information, but just left the foundation, please let me know if you want my full actual codebase and a working live model of this working to get a better idea.

[andrewgauger]

I don't think you can do everything in one query, but you might be able to significantly reduce overhead using:

$setcount=select count(*) from items where gamename='$gamename';
$harvest=select count(*) (DISTINCT item_id) where user_id='$ID' and gamename='$gamename';

 

Now that you don't need to loop through these to get the results you need, you can build a query that returns only meaningful results.  What is it you are looking for, excluding the harvest and setcounts?

[Ken2k7]

I don't quite understand it. From that code, it looks like $haveset and $setcount will always be the same. Also, can you lay out your table structure and explain what you want to SELECT? Basically all the information you want to SELECT without showing a bunch of code because the snippet above is confusing.

[drath]

Ken2k7, Sorry, I messed up, I meant to say:

echo "User has $haveset out of $set items.";

 

andrewgauger, Thanks for those queries, I will try to incorporate them and get back to you on my success/failure.

[andrewgauger]

I think with those you can eliminate your 2 nested queries.

[drath]

Using the count(*) seems to be slowing down the process, in fact, the whole page times out when the query is executed. Is there any alternate ways of counting?

[drath]

I figured out that I don't need to count if I just set the variable to mysql_num_rows($result) after the query. This seems to of fixed the issue; however, I did eventually get the count(*) working anyways. Thanks for the help on this. The query performance is a full second faster now (even on a small set) without the nested counting queries. My final pseudo code as follows:

 

$setcount = "SELECT * FROM items WHERE gamename = '".$gamename."'";
$haveset = "SELECT DISTINCTROW iv.item_id FROM inventory iv INNER JOIN items i ON i.ID = iv.item_id WHERE iv.user_id = '".$ID."' AND i.gamename = '".$gamename."'";

 

SOLVED.