MySQL Forums :: Performance :: Query with 2 x subquery

Advanced Search

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

Subject Views Written By Posted
Query with 2 x subquery 696 Jes Ramsing 04/29/2016 06:44AM
Re: Query with 2 x subquery 391 Øystein Grøvlen 04/29/2016 08:01AM
Re: Query with 2 x subquery 366 Jes Ramsing 05/01/2016 11:57PM
Re: Query with 2 x subquery 322 Øystein Grøvlen 05/02/2016 03:45AM
Re: Query with 2 x subquery 335 Jes Ramsing 05/02/2016 05:32AM
Re: Query with 2 x subquery 336 Rick James 05/01/2016 11:58PM
Re: Query with 2 x subquery 380 Jes Ramsing 05/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.