[RESOLVED] Joining 3 Tables Versus Using Subquery

jsnyder2k

I have a mysql query that works, but it's using a subquery. I'd like to write it using only joins. I have three tables:

risks	
rid	description
1	red
2	blue
3	yellow
4	green
5	black

details		
did	rid	detail
1	1	reddish
2	1	blueish
3	2	reddish
4	3	greenish
5	4	grey

clearance			
cid	rid	user	clearance
1	1	23	1
2	2	25	2
3	2	27	0
4	2	29	2
5	3	25	1

and a value...
user = 25

I would like to show all 'rid' in the 'risks' table where 'detail' = 'reddish' but NOT show ANY rid where user = 25. For instance, with the data given above, if a user is 25, I never want rid = 2 or 5 to show up, only rid 1.

This query works with a subquery:

SELECT r.*, d.* FROM risks r LEFT JOIN details d on d.rid = r.rid
WHERE d.detail = 'reddish'
AND r.rid NOT IN (SELECT rid FROM clearance WHERE user = 25)
GROUP BY r.rid

When I run this query with my full set of data (30,000 rows in clearance and 1,000 rows in risks) the query takes from 80 to 120 seconds. When I remove the subquery, it takes .02 seconds or less.

Any ideas?

Thanks!

Jim

tomhath

The first thing to try is changing the NOT IN to a NOT EXISTS. It's a long explanation why that matters, but it can make a huge difference.

SELECT r.*, d.* FROM risks r LEFT JOIN details d on d.rid = r.rid
WHERE d.detail = 'reddish'
AND  NOT EXISTS (SELECT 1 FROM clearance WHERE user = 25 and r.rid = clearance.rid)
GROUP BY r.rid

jsnyder2k

I've solved this by adding a temporary table. I'm building a temporary table with all of the 'rid' values that are ok to display.

First I'm building an array of 'rid' values that I don't want from the clearance table.

Then I fill an array with the rid values (these are the ones that I don't want displayed). Then I run through the 'risks' table and populate the temporary table with all of the 'rid' values that are NOT in the array:

$query = "CREATE TEMPORARY TABLE `risks_available` (
  `rid` int(10) NOT NULL,
  PRIMARY KEY  (`rid`)
)";
mysql_query($query) or die ("MySQL error0: " . mysql_error());

$query = "SELECT rid FROM clearance WHERE IDCode = 25";
$result = mysql_query($query) or die ("MySQL error0: " . mysql_error());
while ($avoid = mysql_fetch_array($result, MYSQL_BOTH)) {
	$avoid_array[] = $avoid[0];
}
$query = "SELECT rid FROM risks";
$result = mysql_query($query) or die ("MySQL error0: " . mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_BOTH))
{
	if($avoid_array && !in_array($row[0],$avoid_array)) {
		$query = "INSERT INTO risks_available (`rid`) VALUES ('$row[0]')";
		mysql_query($query) or die ("MySQL error4: " . mysql_error());
	}
	// just in case $avoid_array has no values,
	// then I fill the temp table with all of the values
	elseif(!$avoid_array) {
		$query = "INSERT INTO risks_available (`rid`) VALUES ('$row[0]')";
		mysql_query($query) or die ("MySQL error4: " . mysql_error());
	}
}

I end up with a temporary table that I can join to the main table and easily (and quickly) exclude the rid's that I don't want. I'm sure there must me a more elegant solution, but this works and it seems fast. I'll see what happens when the database grows.

Thanks for your help!

Jim

tomhath

Just curious, the NOT EXISTS didn't help? Or you didn't try it? What you have seems very inefficient looping through two result sets and executing a separate query for each row.

jsnyder2k

I didn't try 'exists', but I will. Thanks!

jsnyder2k

tomhath, I tried the NOT EXISTS. I can see why this is a faster way of doing it, but it's simply not giving me the results that I'm looking for. I'm looking to pinpoint which 'rid' values are in the clearance table. When I do a NOT EXISTS, it's basically kicking out a TRUE value if any value exists in the query and giving me an 'all or nothing'. Perhaps it's the way I have the rest of the query written.

I can see how NOT EXISTS is useful and very fast, however. I will certainly use it in the future when a situation calls for it.

Thanks a ton for your help!

Jim