MySQL Forums
Forum List  »  Performance

Re: Query with 2 x subquery
Posted by: Øystein Grøvlen
Date: May 02, 2016 03:45AM

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

Options: ReplyQuote

Written By
April 29, 2016 06:44AM
April 29, 2016 08:01AM
May 01, 2016 11:57PM
Re: Query with 2 x subquery
May 02, 2016 03:45AM
May 02, 2016 05:32AM
May 01, 2016 11:58PM
May 02, 2016 02:16AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.