Suggestion from External Query Advisor gives syntax error

[jda]

I have this query that takes 2-3 seconds to run in our installation:

SELECT d.year,
       d.month,
       specieskey,
       sourcecreated,
       d.locationtype,
       d.statslocation,
       sublocation,
       (SUM(IFMISSINGORNULL(d.hoursfished,0))*60 + SUM(IFMISSINGORNULL(d.minutesfished,0))) minutesfished,
       COUNT(*) count,
       SUM(IFMISSINGORNULL(ARRAY_LENGTH(d.catchkeys),0)) catchcount,
       SUM(CASE WHEN ARRAY_LENGTH(d.catchkeys) = 0 THEN 1 ELSE 0 END) zerotrips
FROM data d
UNNEST (CASE WHEN ARRAY_LENGTH(d.targetspecies) > 0 THEN d.targetspecies ELSE (
        SELECT DISTINCT RAW c.specieskey
        FROM data AS c USE KEYS (ARRAY 'Catch:'|| v FOR v IN d.catchkeys END)) END) specieskey
LET sourcecreated = IFMISSINGORNULL(d.sourcecreated,(CASE WHEN d.userkey IS NOT VALUED THEN '4' ELSE (CASE WHEN UPPER(d.`key`)=d.`key` THEN '1' ELSE '2' END) END)),
    sublocation = d.assoczonekey
WHERE d.type='FishingTrip'
    AND d.assockey='1'
    AND d.statspublic
    AND specieskey IS VALUED
    AND d.locationtype IS VALUED
    AND d.statslocation IS VALUED
    AND d.year IS VALUED
    AND d.month IS VALUED
GROUP BY d.year,
         d.month,
         specieskey,
         sourcecreated,
         d.locationtype,
         d.statslocation,
         sublocation,
         specieskey,
         sourcecreated
ORDER BY d.year DESC,
         d.month,
         d.locationtype,
         d.statslocation,
         sublocation,
         specieskey,
         sourcecreated

So I decided to use the External Query Advisor to suggest an index that would perform better. It returns this query to create an index:

CREATE INDEX adv_ALL_casewhen_0array_length_d_targetspecies_then_d_targetspec2301370714 ON `data`(ALL case when (0 < array_length((`targetspecies`))) then (`targetspecies`) else correlated (select distinct raw (`c`.`specieskey`) from `default`:`data` as `c` use keys array ('Catch:' || `v`) for `v` in (`catchkeys`) end) end,`type`,`assockey`,`statspublic`,`month`,`statslocation`,`locationtype`,`year`)

However, when I try to create that index I get a syntax error:

{
  "errors": [
    {
      "code": 3000,
      "msg": "syntax error - line 1, column 136, near 'targetspecies`) ELSE', at: CORRELATED (reserved word)",
      "query_from_user": "explain CREATE INDEX adv_targetspecies_then_ ON `data`(ALL CASE WHEN (0 < ARRAY_LENGTH((`targetspecies`))) THEN (`targetspecies`) ELSE CORRELATED (\n        SELECT DISTINCT RAW (`c`.`specieskey`)\n        FROM `default`:`data` AS `c` USE KEYS ARRAY('Catch:' || `v`) FOR `v` IN (`catchkeys`) END) END,\n    `type`,\n    `assockey`,\n    locationlevel2,\n    locationlevel4,\n    `statspublic`,\n    `month`,\n    `statslocation`,\n    `locationtype`,\n    `year`)"
    }
  ]
}

Any ideas as to how to build an appropriate index?

I am on Community Edition 7.2.4 build 7070 if that makes a difference…

[mreiche]

I see the same issue with the query from External Index Advisor. The position indicated is the beginning of “correlated”, and I can’t find any sql++ examples that use “correlated”.

The 7.6.1 Query Advisor recommends:

Main Query Advice

CREATE INDEX adv_assockey_type_statspublic_statslocation_month_year_locationtype ON `data`(`assockey`,`type`,`statspublic`,`statslocation`,`month`,`year`,`locationtype`)

Subquery Advice

Subquery: select distinct raw (`c`.`specieskey`) from `default`:`data` as `c` use keys array ("Catch:" || `v`) for `v` in (`d`.`catchkeys`) end

[jda]

Thanks for checking that.

I am a little uncertain as to how to. use the “subquery advice”. Does that mean that I should change parenthesises of my UNNEST subquery to match the advice?

I created the index as per the Main Query Advice and I see that the query uses it.

Then I tried to adjust the UNNEST subquery. It takes around 2.7 secs (on my small test servers). and the original one takes 2.8 (out of three - one on 2.7). So likely a small improvement so I guess that answers my first question as “yes I should use the slightly changed syntax”

But still a little slow… It is probably Ok for this query as it is used sparsely by an admin user - but I’m just getting used to being able to improve query performance by magnitudes

[jda]

Actually, it is not consistent. I tried to change the order of assockey,type in the index and then if anything the first sub query is 0.1 sec faster…

[vsr1]

Subquery is correlated and use keys. No index required

CREATE INDEX ix1 ON `data`(`assockey`,`statspublic`)
 where type='FishingTrip';