Re: Query with 2 x subquery
Hi Jes,
Your problem with the current query plan is that the query will go through all rows in data_i16 since the restriction on part and session_id is inside a subquery. (By the way, why do you have ORDER BY in subquery)?
In MySQL 5.6 we introduced semi-join that make it possible to process IN-subqueries "inside-out"; i.e., process tables of the subquery before tables of the outer query. However, semi-join can not be used when IN-subquery contains GROUP BY.
In this case, we can manually transform the outer IN-expression to a JOIN. Due to the GROUP BY, we know that the result of "SELECT MAX(sessionid) as sessionid, varid ..." will contain unique rows. Hence, we do not need to worry that a JOIN will give duplicate matches, and we can transform the query to:
SELECT sessionid, varid, val FROM data_i16 JOIN
(SELECT MAX(sessionid) as sessionid, varid FROM data_i16 WHERE sessionid IN
(SELECT sessionid FROM session WHERE partid='5' AND sessionid<1611659) GROUP BY varid) tmax
USING (sessionid, varid);
Also, you should probably add an index on partid. Otherwise, MySQL will need to go through all sessions with sessionid lower than the given number. (You can also drop the second index on sessionid.)
MySQL has developers all over the world, but here in Trondheim we even have an office!
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway