Hi I have the below N1QL queries. I need a single covering index that will work for both the queries.
Below is the index I will provide the dba team
CREATE INDEX apiExt_prtrApp_compId_C ON `api_external`((`data`.`companyId`),(`data`.`additionalAdmins`),(`data`.`additionalDevelopers`),`uuid`,(`data`.`appName`),(`data`.`description`),(`data`.`fourthParty`), `deleted`) WHERE (`type` = 'partnerApp')
CREATE INDEX apiExt_intg_prtrAppId_C_1 ON `api_external`(`deleted`,`backupOf`,(`data`.`partnerAppId`),(`data`.`env`),(`data`.`productStatus`),`updatedTs`,`createdTs`,(`data`.`productId`),(`data`.`status`)) WHERE (`type` = 'integration')
Below are the queries. Will the above index work for both queries as covering index ?
SELECT t3.id,
t3.description,
t3.dcrFlag,
t3.lastModified,
t3.name,
(SELECT p1.*,
p.data.displayName AS productName,
p.uuid productId
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
FROM (SELECT t2.id,
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,
coalesce(m.updatedTs,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 ("73fbc0a557b54732a991f4bb7dbda327" in pa.data.additionalAdmins OR "73fbc0a557b54732a991f4bb7dbda327" in pa.data.additionalDevelopers)
AND pa.deleted = false
AND i1.deleted = false
AND i1.data.env !="QA"
AND (IS_MISSING(i1.backupOf)
OR IS_NULL (i1.backupOf)
OR i1.backupOf="")
GROUP BY pa.uuid, i1.data.productStatus LETTING m = MIN([i1.data.env, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, 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
SELECT t3.id,
t3.description,
t3.dcrFlag,
t3.lastModified,
t3.name,
(SELECT p1.*,
p.data.displayName AS productName,
p.uuid productId
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
FROM (SELECT t2.id,
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,
coalesce(m.updatedTs,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 = "73fbc0a557b54732a991f4bb7dbda327"
AND pa.deleted = false
AND i1.deleted = false
AND i1.data.env !="QA"
AND (IS_MISSING(i1.backupOf)
OR IS_NULL (i1.backupOf)
OR i1.backupOf="")
GROUP BY pa.uuid, i1.data.productStatus LETTING m = MIN([i1.data.env, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, 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;
GROUP BY t2.id) AS t3;
