Yes the use additional index makes sense as it forms a covering index in a sense.
I will give this a try today and report back on the results. -b On Sun, Jun 26, 2016 at 6:13 AM, Sergi Vladykin <[email protected]> wrote: > Barrett, > > For this exact query you will achieve the best results if you will have > two group indexes: (internalRobotName, internalTimeStamp DESC) and ( > internalRobotName, internalMissionId). Also it will make sense to change > ordering to > > ORDER BY internalRobotName, internalTimeStamp DESC > > This way you will have the same query semantics but use index sort. > > Sergi > > On Sun, Jun 26, 2016 at 12:43 PM, Sergi Vladykin <[email protected] > > wrote: > >> Andrey, >> >> Did you try these "tricks" with Ignite? I think not, because they will >> not work. >> Please stop suggesting to our users anything that you did not prove >> yourself to be a correct solution. >> >> Sergi >> >> On Sun, Jun 26, 2016 at 11:14 AM, AndreyVel <[email protected]> wrote: >> >>> H2 engine chooses index internalRobotName_idx because you have expression >>> WHERE (S1.INTERNALROBOTNAME = 'gp1') >>> In current H2 version it's not possible to specify index hints. You can >>> use >>> same tricks. >>> >>> For complex queries more convenient to use temp tables. >>> >>> CREATE TEMP TABLE IF NOT EXISTS TEMP(ID INT PRIMARY KEY, NAME >>> VARCHAR); >>> CREATE INDEX IF NOT EXISTS IDX_TEMP_NAME ON TEMP(NAME); >>> >>> INSERT INTO TEMP(ID, NAME) >>> SELECT P1.ID, P1.FIRST_NAME FROM PERSON P1 WHERE ... >>> >>> >>> H2 SQL Grammar http://www.h2database.com/html/grammar.html#create_table >>> >>> >>> >>> -- >>> View this message in context: >>> http://apache-ignite-users.70518.x6.nabble.com/Correlated-Subquery-Performance-tp5877p5898.html >>> Sent from the Apache Ignite Users mailing list archive at Nabble.com. >>> >> >> >
