Yes. The first way is performant enough.
I tried the latter and get the plan below.
It appears the optimizer is not using the internalMissionId index?
I have it defined as an additional standalone index such as
@QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
name = "robot_timestamp_idx", order = 3)})
private String internalMissionId;
SELECT
S1._KEY,
S1._VAL,
S1.INTERNALROBOTNAME,
S1.INTERNALTIMESTAMP,
S1.INTERNALISCRITICAL,
S1.INTERNALMISSIONID
FROM "HotTelemetry".STATEFULROBOTTELEMETRY S1
/* "HotTelemetry"."internalRobotName_idx": INTERNALROBOTNAME = 'gp1' */
/* WHERE S1.INTERNALROBOTNAME = 'gp1'
*/
INNER JOIN (
SELECT
INTERNALMISSIONID
FROM "HotTelemetry".STATEFULROBOTTELEMETRY S3
/* "HotTelemetry"."internalRobotName_idx": INTERNALROBOTNAME =
'gp1' */
WHERE S3.INTERNALROBOTNAME = 'gp1'
ORDER BY =S3.INTERNALTIMESTAMP DESC
LIMIT 1
) S2
/* SELECT
INTERNALMISSIONID
FROM "HotTelemetry".STATEFULROBOTTELEMETRY S3
/++ "HotTelemetry"."internalRobotName_idx": INTERNALROBOTNAME =
'gp1' ++/
WHERE S3.INTERNALROBOTNAME = 'gp1'
ORDER BY =S3.INTERNALTIMESTAMP DESC
LIMIT 1
*/
ON 1=1
WHERE (S1.INTERNALROBOTNAME = 'gp1')
AND (S2.INTERNALMISSIONID = S1.INTERNALMISSIONID)
On Fri, Jun 24, 2016 at 10:27 AM, AndreyVel <[email protected]> wrote:
> Hello bearrito,
>
> What Execution plan in you query, indexes used?
> https://apacheignite.readme.io/docs/sql-queries#using-explain
> You query can split to 2 different query
>
> SELECT internalMissionId FROM STATEFULROBOTTELEMETRY S2 WHERE
> S2.internalRobotName = ? ORDER BY internalTimeStamp DESC LIMIT 1
> and
> SELECT S1.* FROM STATEFULROBOTTELEMETRY S1 where S1.internalRobotName = ?
> and internalMissionId = ?
>
> also can be help rewriting qury to
>
> SELECT S1.* FROM STATEFULROBOTTELEMETRY S1
> INNER JOIN (
> SELECT INTERNALMISSIONID FROM STATEFULROBOTTELEMETRY S3
> WHERE S3.INTERNALROBOTNAME = ? ORDER BY S3.INTERNALTIMESTAMP DESC
> LIMIT 1)
> S2 ON S2.INTERNALMISSIONID = S1.INTERNALMISSIONID
> WHERE S1.INTERNALROBOTNAME = ?
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Correlated-Subquery-Performance-tp5877p5886.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
