Re: Query with 2 x subquery

[Jes Ramsing]

First things first :-)
Dual order was a leftover from experimenting with the query.
The query you have compiled is brilliant and solves the problem.

Results are identical, yours handle the data in 0,92secs, mine took 29,14secs...

Øystein (~1sec):
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<1615000) GROUP BY varid) tmax
USING (sessionid, varid);

Jes (~29secs):
SELECT sessionid, varid, val FROM data_i16 WHERE ROW(sessionid, varid) IN
(SELECT MAX(sessionid) as sessionid, varid FROM data_i16 WHERE sessionid IN
(SELECT sessionid FROM session WHERE partid='5' AND sessionid<1615000) GROUP BY varid ORDER BY sessionid DESC, varid);

EXPLAIN of fast:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL 5686266 Using where
1 PRIMARY data_i16 eq_ref PRIMARY,ui16_idx PRIMARY 12 tmax.varid,tmax.sessionid 1
2 DERIVED session range PRIMARY,Index 1 PRIMARY 8 947711 Using where; Using temporary; Using filesort
2 DERIVED data_i16 ref PRIMARY,ui16_idx ui16_idx 8 nodejstest.session.sessionid 6 Using index

EXPLAIN of slow:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY data_i16 ALL 19958655 Using where
2 SUBQUERY session range PRIMARY,Index 1 PRIMARY 8 947711 Using where; Using temporary; Using filesort
2 SUBQUERY data_i16 ref PRIMARY,ui16_idx ui16_idx 8 nodejstest.session.sessionid 6 Using index

Thank you for your help, much appreciated.