Covering index recommendation for IF_MISSING_OR_NULL, TO_NUM, OBJECT_INNER_PAIRS

SQL++
1

Hi,

@vsr1 I was hopeing you could help me with the below query.

I need to get data for 500 products and the query is taking like 8 seconds. Do you think it is possible to make a covering index to cover the needed values without having to fetch the documents at all?

SELECT i_genre_id,
       IF_MISSING_OR_NULL(key1 , 0) AS something,
       IF_MISSING_OR_NULL( TO_NUM(OBJECT_INNER_PAIRS(key2.subkey1.['sub-subkey3'])[-1].name), NULL) AS dl_unix_timestamp,
       IF_MISSING_OR_NULL( OBJECT_INNER_PAIRS(key2.subkey1.['sub-subkey3'])[-1].val, NULL) AS dl,
       IF_MISSING_OR_NULL( TO_NUM(OBJECT_INNER_PAIRS(key4.subkey5.['sub-subkey3'])[-1].name), NULL) AS dau_unix_timestamp,
       IF_MISSING_OR_NULL( OBJECT_INNER_PAIRS(key4.subkey5.['sub-subkey3'])[-1].val, NULL) AS dau,
       IF_MISSING_OR_NULL( gpfs, NULL) AS gpfs,
       IF_MISSING_OR_NULL( TO_NUM( gvs), NULL) AS gvs,
FROM `products` where (product_id= "123" or  product_id= "234" or  product_id= "345") and sub_type= "products_extra"

the sub-subkey3 should be part of the WHERE clause. it can have multiple values…

Thank you,

2

Not sure how many documents it qualifies but you can try the following and see

CREATE INDEX ix1 ON products(product_id)  WHERE sub_type= "products_extra";

SELECT i_genre_id,
       IF_MISSING_OR_NULL(key1 , 0) AS something,
       IF_MISSING_OR_NULL( TO_NUM(OBJECT_INNER_PAIRS(key2.subkey1.['sub-subkey3'])[-1].name), NULL) AS dl_unix_timestamp,
       IF_MISSING_OR_NULL( OBJECT_INNER_PAIRS(key2.subkey1.['sub-subkey3'])[-1].val, NULL) AS dl,
       IF_MISSING_OR_NULL( TO_NUM(OBJECT_INNER_PAIRS(key4.subkey5.['sub-subkey3'])[-1].name), NULL) AS dau_unix_timestamp,
       IF_MISSING_OR_NULL( OBJECT_INNER_PAIRS(key4.subkey5.['sub-subkey3'])[-1].val, NULL) AS dau,
       IF_MISSING_OR_NULL( gpfs, NULL) AS gpfs,
       IF_MISSING_OR_NULL( TO_NUM( gvs), NULL) AS gvs,
FROM `products` 
WHERE  product_id IN  ["123", "234", "345"] and sub_type= "products_extra"

CREATE INDEX ix2 ON products(product_id, i_genre_id, key1, gpfs, gvs,OBJECT_INNER_PAIRS(key2.subkey1.`sub-subkey3`)[-1], OBJECT_INNER_PAIRS(key4.subkey5.`sub-subkey3`)[-1] ) WHERE sub_type= "products_extra";

SELECT i_genre_id,
       IF_MISSING_OR_NULL(key1 , 0) AS something,
       IF_MISSING_OR_NULL( TO_NUM(k1.name), NULL) AS dl_unix_timestamp,
       IF_MISSING_OR_NULL(k1.val, NULL) AS dl,
       IF_MISSING_OR_NULL( TO_NUM(k2.name), NULL) AS dl_unix_timestamp,
       IF_MISSING_OR_NULL(k2.val, NULL) AS dl,
       IF_MISSING_OR_NULL( gpfs, NULL) AS gpfs,
       IF_MISSING_OR_NULL( TO_NUM( gvs), NULL) AS gvs,
FROM `products`  AS p
LET k1 = OBJECT_INNER_PAIRS(key2.subkey1.`sub-subkey3`)[-1]
    k2 = OBJECT_INNER_PAIRS(key4.subkey5.`sub-subkey3`)[-1]
WHERE  product_id IN  ["123", "234", "345"] and sub_type= "products_extra"

FYI: As you doing OBJECT_INNER_PAIRS()[-1] the array sorted by name. Hope that is what you want

3

Thanks vsr1

related to this:

does it mean that I need to create an index for each sub-subkey3? There are around 45 different values

4

As you are not using any predicate on those it may be worth doing Fetch due to many combinations.
covering indexes can strain indexer due to number of entries even come up alternative.
Did you try first index and query and see.