N1QL performance covered index

I have the below Query - that i want to run for my application.
Can You please suggest covered index for this to give optimal performance.
Data is low and hence we can afford covered indexes

SELECT t3.id, t3.description, t3.dcrFlag, t3.lastModified, t3.name,
       (SELECT p1.*, p.data.displayName AS productName, p.uuid
        FROM api_external AS p USE KEYS t3.products[*].product
        LET p1 = FIRST {v.env, v.status} FOR v IN t3.products WHEN META(p).id == v.product END) AS products
           ARRAY_AGG({"product":"product::"||t2.product, t2.env, t2.status}) AS products,
           MIN({t2.description, t2.dcrFlag, t2.lastModified, t2.name}).*
      FROM (SELECT t1.id,product,
                 MIN(t1.env) AS env,
                 MAX(status) AS status,
                 MIN({t1.description, t1.dcrFlag, t1.lastModified, t1.name}).*
            FROM (SELECT m.appName AS name, m.uuid AS id, m.description, m.env, m.fourthParty AS dcrFlag,
                         m.createdTs AS lastModified, m.productStatus , m.productId , m.status
                    FROM api_external AS pa
                    JOIN api_external AS i1 ON pa.uuid = i1.data.partnerAppId AND i1.type = "integration"
                    WHERE pa.type = "partnerApp" AND pa.data.companyId = "<companyID>"
                    GROUP BY pa.uuid, i1.data.productStatus
                    LETTING m = MAX([i1.createdTs, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty,
                                                    i1.data.env, i1.data.productStatus , i1.createdTs,
                                                    i1.data.productId , i1.data.status }])[1]
                 ) AS t1
            LEFT UNNEST OBJECT_PAIRS(t1.productStatus) AS ps
            LET product = IFMISSINGORNULL(t1.productId, ps.name),
                status = IFMISSINGORNULL(t1.status, ps.val)
            GROUP BY t1.id, product) AS t2
      GROUP BY t2.id) AS t3;

@dh or @vsr1 let me know ur thoughts on this. Your help on this is much appreciated.

I have created the following indexes but i want to see if there is a better index that i can prepare for better performance

CREATE INDEX idx_data_companyId_appName_desc_fourthParty ON `api_external`((`data`.`companyId`),`uuid`,(`data`.`appName`),(`data`.`description`),(`data`.`fourthParty`)) WHERE (`type` = 'partnerApp')
CREATE INDEX idx_partnerAppId_env_productStatus ON `api_external`((`data`.`partnerAppId`),(`data`.`env`),(`data`.`productStatus`)) WHERE (`type` = 'integration')
CREATE INDEX idx_partnerAppId_env_productStatus ON `api_external` (data.partnerAppId,data.env,data.productStatus, createdTs, data.productId , data.status ) 
WHERE (`type` = 'integration')

@vsr1 do I replace the current index or this is an additional index ?

replace as additional keys added at the end to cover. check if query covering or not

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.