Complex Query takes much longer than the two simple queries

Databases
1

I am trying to do a query that depends on a set of id’s from one table and then is used in a second query. I would like to, and I’m thinking it is proper to do it in one query. When I run them separately, the run in .0013 and .0074 seconds. But when I run them together it takes 3.89 seconds.

The first two are the individual queries and the third is the combined query that really only substitutes the second query for the in clause that has the actual numbers.

Thanks for any input.


SELECT d1.dname, d1.d_id, v1.v_id, v1.name as rname, v1.city, v1.state, v1.latitude, v1.longitude, ds1.votes, ds1.totalpoints, ds1.totalpoints/ds1.votes as score, GROUP_CONCAT(DISTINCT vc1.cuisine SEPARATOR ', ') as cuisine					
		FROM ds1 
		INNER JOIN d1 on ds1.d_id = d1.d_id  
		INNER JOIN v1 on ds1.v_id = v1.v_id and v1.v_id in (949578,949633,949658,949662,949651,959348) 
		LEFT JOIN vc1 on vc1.v_id = v1.v_id
		GROUP BY d1.d_id, v1.v_id
		ORDER BY d1.d_name, score desc, rname



select DISTINCT(v.v_id) from v1 AS v
		INNER JOIN r on r.v_id = v.v_id and r.u_id = 101 
		WHERE v.state = 'AK'

Combined query


SELECT d1.dname, d1.d_id, v1.v_id, v1.name as rname, v1.city, v1.state, v1.latitude, v1.longitude, ds1.votes, ds1.totalpoints, ds1.totalpoints/ds1.votes as score, GROUP_CONCAT(DISTINCT vc1.cuisine SEPARATOR ', ') as cuisine					
		FROM ds1 
		INNER JOIN d1 on ds1.d_id = d1.d_id  
		INNER JOIN v1 on ds1.v_id = v1.v_id and v1.v_id in (SELECT DISTINCT(v.v_id) from v1 as v
		INNER JOIN r on r.v_id = v.v_id and r.u_id = 101 
		WHERE v.state = 'AK') 
		LEFT JOIN vc1 on vc1.v_id = v1.v_id
		GROUP BY d1.d_id, v1.v_id
		ORDER BY d1.d_name, score desc, rname
2

This looks nothing like what I posted. I will post it without code tags below and see what happens

I am trying to do a query that depends on a set of id’s from one table and then is used in a second query. I would like to, and I’m thinking it is proper to do it in one query. When I run them separately, the run in .0013 and .0074 seconds. But when I run them together it takes 3.89 seconds.

The first two are the individual queries and the third is the combined query that really only substitutes the second query for the in clause that has the actual numbers.

Thanks for any input.

code
SELECT d1.dname, d1.d_id, v1.v_id, v1.name as rname, v1.city, v1.state, v1.latitude, v1.longitude, ds1.votes, ds1.totalpoints, ds1.totalpoints/ds1.votes as score, GROUP_CONCAT(DISTINCT vc1.cuisine SEPARATOR ', ') as cuisine
FROM ds1
INNER JOIN d1 on ds1.d_id = d1.d_id
INNER JOIN v1 on ds1.v_id = v1.v_id and v1.v_id in (949578,949633,949658,949662,949651,959348)
LEFT JOIN vc1 on vc1.v_id = v1.v_id
GROUP BY d1.d_id, v1.v_id
ORDER BY d1.d_name, score desc, rname

code
select DISTINCT(v.v_id) from v1 AS v
INNER JOIN r on r.v_id = v.v_id and r.u_id = 101
WHERE v.state = ‘AK’

code
SELECT d1.dname, d1.d_id, v1.v_id, v1.name as rname, v1.city, v1.state, v1.latitude, v1.longitude, ds1.votes, ds1.totalpoints, ds1.totalpoints/ds1.votes as score, GROUP_CONCAT(DISTINCT vc1.cuisine SEPARATOR ', ') as cuisine
FROM ds1
INNER JOIN d1 on ds1.d_id = d1.d_id
INNER JOIN v1 on ds1.v_id = v1.v_id and v1.v_id in (SELECT DISTINCT(v.v_id) from v1 as v
INNER JOIN r on r.v_id = v.v_id and r.u_id = 101
WHERE v.state = ‘AK’)
LEFT JOIN vc1 on vc1.v_id = v1.v_id
GROUP BY d1.d_id, v1.v_id
ORDER BY d1.d_name, score desc, rname