Index for query with LET

SQL++
1

I am trying to create covered index for query that have LET. When I run Explain command, I dont see that index being applied. I am not certain what I am missing in the Index query statement. Is it possible to index statement with LET

Query to create Index:

CREATE INDEX match_shots_idx_ ON CourtTracking (context.tournament.tournYear, context.tournament.Id, 
context.match.tourId, valid, type, serve, `set`, game,  p.context.server.Id,
p.context.receiver.Id, p.context.scorer.Id, p.context.gameScore.s, context.serveClass.c,
DISTINCT ARRAY {s.Id, s.shotType} FOR s in shot END) WHERE (`type` = 'point' AND shot IS NOT MISSING);

The query:

SELECT RAW
       CASE WHEN serveReturned[0] IS NOT null AND serveReturned[0]>0 THEN ROUND((serveReturned[0]/serve[0])*100, 2) 
	   ELSE 0
	   END 
LET serveReturned = (SELECT  RAW COUNT(*) 
		FROM CourtTracking p 
		WHERE p.context.tournament.tournYear = 99999
				AND p.context.tournament.Id = 9999
				AND p.context.match.tourId =  '9'
				AND p.valid = true
				AND p.type='point'
				AND p.serve = 1
				AND p.`set`!= 0
				AND p.context.serveClass.c='RETURNED'),

 serve = (SELECT  RAW COUNT(*) 
		FROM CourtTracking p 
		WHERE p.context.tournament.tournYear = 99999
				AND p.context.tournament.Id = 9999
				AND p.context.match.tourId =  '9'
				AND p.valid = true
				AND p.type='point'
				AND p.serve = 1
				AND p.`set`!= 0
				AND array_contains(['RETURNED', 'ACE', 'NOT RETURNED','NOT LOGGED'], p.context.serveClass.c))
2

Well, to begin, with remove the ā€œpā€ from these four expressions in the CREATE INDEX. The ā€œpā€ is the alias in the query for the CourtTracking bucket; it should not appear in the CREATE INDEX.

p.context.server.Id,
p.context.receiver.Id, p.context.scorer.Id, p.context.gameScore.s

3

In Addition to @johan_larson, At present only EXPLAIN is showed from clause subquery only. For All others you can do EXPLAIN on subquery (if not correlated, correlated replace reference with $xx), That is what internally used.

1 Like