ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Barrett Strausser <barr...@bnrobotics.com>
Subject Re: Correlated Subquery Performance
Date Sun, 26 Jun 2016 12:52:18 GMT
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 <sergi.vladykin@gmail.com>
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 <sergi.vladykin@gmail.com
> > 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 <andrey4vel@gmail.com> 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.
>>>
>>
>>
>

Mime
View raw message