Complex query performance optimization

Vladlan · February 4, 2022, 10:11pm

Hi!
maybe somebody know how to improve such query?

Indexes Currently Used
CREATE INDEX prod_adv_originalSource_timestamp_lower_status_code_class 
ON `production`(lower(`status`),`code`,`timestamp`) 
WHERE ((`_class` = 'model.Transaction') and (`originalSource` = 'S1'))

CREATE INDEX prod_adv_currency_originalSource_timestamp_lower_status_transaction 
ON `production`(lower(`status`),`currency`,`timestamp`,`transactionTotal`,`code`,to_number(`transactionTotal`)) 
WHERE ((`_class` = 'model.Transaction') and (`originalSource` = 'S1'))

CREATE INDEX prod_adv_originalSource_currency_timestamp_lower_status_to_number 
ON `production`(`originalSource`,`currency`,`timestamp`,lower(`status`),to_number(`transactionTotal`)) 
WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'))

SELECT AVG(TONUMBER(st.transactionTotal)) AS amount
FROM (
    SELECT trs.transactionTotal,
           trs.consumerId
    FROM production trs
    WHERE trs._class = "model.Transaction"
        AND trs.originalSource = "S1"
        AND trs.code = "C1"
        AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
        AND LOWER(trs.status) != "deleted"
        AND trs.currency = "GBP") AS st
    JOIN production AS cmrs ON KEYS st.consumerId
WHERE (cmrs.marketingPreferences IS NOT VALUED
        OR ( (cmrs.marketingPreferences.email = FALSE
                OR cmrs.marketingPreferences.email IS NOT VALUED)
            AND (cmrs.marketingPreferences.post = FALSE
                OR cmrs.marketingPreferences.post IS NOT VALUED)
            AND (cmrs.marketingPreferences.sms = FALSE
                OR cmrs.marketingPreferences.sms IS NOT VALUED)
            AND (cmrs.marketingPreferences.social = FALSE
                OR cmrs.marketingPreferences.social IS NOT VALUED)
            AND (cmrs.marketingPreferences.telephone = FALSE
                OR cmrs.marketingPreferences.telephone IS NOT VALUED) ) )

which takes 38 seconds.

Using Couchbase Enterprise Edition 6.6.3 build 9808.
DB has 170,655,089 items, 104GB / 126GB RAM, 541GB HDD. (1 bucket, 6 servers (2 for data, 4 for query and index)).

Thanks in advance!

vsr1 · February 4, 2022, 10:50pm

Your index missing consumerId to cover.
If you are going to use LOWER(status) != “deleted” all the time move index WHERE.

CREATE INDEX ix1 ON `production`(`originalSource`,`currency`,`timestamp`,`transactionTotal`, consumerId)
WHERE (`_class` = 'model.Transaction') and (`code` = 'C1') AND LOWER(status) != "deleted"

cmrs do you have any type or _class name so that we can avoid scan whole production.
How many do u think those will be with WHERE clause?

If u have specific _class ix3 add that in index WHERE and query. And see if WITH clause query covered by running separate EXPLAIN and see how long it takes. If it generates too many then go back to JOIN query.

Then WHOLE query should covered.

CREATE INDEX ix2 ON `production`(`originalSource`,`currency`,`timestamp`,`transactionTotal`, `consumerId`)
          WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'))

CREATE INDEX ix3 ON production(IFMISSINGORNULL(marketingPreferences.telephone,FALSE))
                 WHERE IFMISSINGORNULL(marketingPreferences.email,FALSE) = FALSE
                       AND IFMISSINGORNULL(marketingPreferences.post,FALSE) = FALSE
                       AND IFMISSINGORNULL(marketingPreferences.post,sms) = FALSE
                       AND IFMISSINGORNULL(marketingPreferences.social,FALSE) = FALSE;



SELECT AVG(TONUMBER(st.transactionTotal)) AS amount
FROM ( WITH consumerIds AS (SELECT RAW META(cmrs).id
                            FROM production AS cmrs
                            WHERE IFMISSINGORNULL(marketingPreferences.email,FALSE) = FALSE
                                  AND IFMISSINGORNULL(marketingPreferences.post,FALSE) = FALSE
                                  AND IFMISSINGORNULL(marketingPreferences.post,sms) = FALSE
                                  AND IFMISSINGORNULL(marketingPreferences.social,FALSE) = FALSE
                                  AND IFMISSINGORNULL(marketingPreferences.telephone,FALSE) = FALSE)
       SELECT trs.transactionTotal, trs.consumerId IN consumerIds AS flag
       FROM production trs
       WHERE trs._class = "model.Transaction"
             AND trs.originalSource = "S1"
             AND trs.code = "C1"
             AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
             AND LOWER(trs.status) != "deleted"
             AND trs.currency = "GBP") AS st
st.flag = TRUE;

Vladlan · February 5, 2022, 2:36pm

@vsr1 thank you for response.

Do you think it is possible to execute your query in less than 30 seconds if query

CREATE INDEX prod_adv_ifmissingornull_marketingPreferences_all_false_C1 
ON `production`(
ifmissingornull((`marketingPreferences`.`email`), false),
ifmissingornull((`marketingPreferences`.`social`), false),
ifmissingornull((`marketingPreferences`.`post`), false),
ifmissingornull((`marketingPreferences`.`telephone`), false),
ifmissingornull((`marketingPreferences`.`sms`), false),
`forename`
) 
WHERE ((`_class` = 'model.Consumer') and (`code` = 'C1'))

SELECT COUNT(cmrs)
FROM production AS cmrs
WHERE cmrs._class = "model.Consumer"
    AND cmrs.code = "C1"
    AND cmrs.forename IS VALUED
    AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE

takes 33.4 seconds.

Your query (which runs endlessly):

Index used:

CREATE INDEX prod_adv_originalSource_currency_timestamp_lower_status_to_numbe38690260 
ON `production`(`timestamp`,to_number(`transactionTotal`),`consumerId`) 
WHERE (((((`_class` = 'model.Transaction') and (`code` = 'C1')) and (not (lower(`status`) = 'deleted'))) and (`originalSource` = 'S1')) and (`currency` = 'GBP'))

CREATE INDEX prod_adv_originalSource_currency_timestamp_lower_status_to_numbe1377909695 
ON `production`(`originalSource`,`currency`,`timestamp`,lower(`status`),to_number(`transactionTotal`)) 
WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'))

CREATE INDEX prod_adv_originalSource_timestamp_lower_status_code_class 
ON `production`(lower(`status`),`code`,`timestamp`) 
WHERE ((`_class` = 'model.Transaction') and (`originalSource` = 'S1'))

CREATE INDEX prod_adv_currency_originalSource_timestamp_lower_status_transaction 
ON `production`(lower(`status`),`currency`,`timestamp`,`transactionTotal`,`code`,to_number(`transactionTotal`)) 
WHERE ((`_class` = 'model.Transaction') and (`originalSource` = 'S1'))

SELECT AVG(TONUMBER(st.transactionTotal)) AS amount
FROM ( WITH consumerIds AS (SELECT RAW META(cmrs).id
                            FROM production AS cmrs
                            WHERE cmrs._class = "model.Consumer"
                                  AND cmrs.code = "C1"
                                  AND cmrs.forename IS VALUED
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.post,sms) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE)
       SELECT trs.transactionTotal, trs.consumerId IN consumerIds AS flag
       FROM production trs
       WHERE trs._class = "model.Transaction"
             AND trs.originalSource = "S1"
             AND trs.code = "C1"
             AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
             AND LOWER(trs.status) != "deleted"
             AND trs.currency = "GBP") AS st
WHERE st.flag = TRUE;

vsr1 · February 5, 2022, 2:45pm

@Vladlan ,

Try COUNT(1) if use alias it may mean whole document may not cover, explain and see if it using covering.

It might too much of documents then try your original query with

CREATE INDEX ix1 ON `production`(`originalSource`,`currency`,`timestamp`, LOWER(status), `transactionTotal`, consumerId)
WHERE (`_class` = 'model.Transaction') and (`code` = 'C1')

Vladlan · February 5, 2022, 6:57pm

@vsr1 I am not quite pro in it

Used explain for

SELECT COUNT(1)
FROM ( WITH consumerIds AS (SELECT RAW META(cmrs).id
                            FROM production AS cmrs
                            WHERE cmrs._class = "model.Consumer"
                                  AND cmrs.code = "C1"
                                  AND cmrs.forename IS VALUED
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE)
       SELECT trs.transactionTotal, trs.consumerId IN consumerIds AS flag
       FROM production trs
       WHERE trs._class = "model.Transaction"
             AND trs.originalSource = "S1"
             AND trs.code = "C1"
             AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
             AND LOWER(trs.status) != "deleted"
             AND trs.currency = "GBP") AS st
WHERE st.flag = TRUE

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "With",
            "bindings": [
                {
                    "expr": "(select raw (meta(`cmrs`).`id`) from `production` as `cmrs` where (((((((((`cmrs`.`_class`) = \"model.Consumer\") and ((`cmrs`.`code`) = \"C1\")) and ((`cmrs`.`forename`) is valued)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`email`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`post`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`sms`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`social`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`telephone`), false) = false)))",
                    "static": true,
                    "var": "consumerIds"
                }
            ],
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "UnionScan",
                        "scans": [
                            {
                                "#operator": "IntersectScan",
                                "scans": [
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_timestamp_lower_status_code_class",
                                        "index_id": "43c5d0524bc07061",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"deleted\"",
                                                        "inclusion": 0,
                                                        "low": "null"
                                                    },
                                                    {
                                                        "high": "\"C1\"",
                                                        "inclusion": 3,
                                                        "low": "\"C1\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_currency_timestamp_lower_status_to_number_transactionTotal_class_code_C1_V2",
                                        "index_id": "a63ad08fa500a310",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_currency_timestamp_lower_status_to_number_transactionTotal_class_code_C1",
                                        "index_id": "745b2cfca33bce19",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"S1\"",
                                                        "inclusion": 3,
                                                        "low": "\"S1\""
                                                    },
                                                    {
                                                        "high": "\"GBP\"",
                                                        "inclusion": 3,
                                                        "low": "\"GBP\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    },
                                                    {
                                                        "high": "\"deleted\"",
                                                        "inclusion": 0,
                                                        "low": "null"
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_currency_originalSource_timestamp_lower_status_transaction",
                                        "index_id": "1240c741f21f2520",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"deleted\"",
                                                        "inclusion": 0,
                                                        "low": "null"
                                                    },
                                                    {
                                                        "high": "\"GBP\"",
                                                        "inclusion": 3,
                                                        "low": "\"GBP\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    },
                                                    {
                                                        "inclusion": 0
                                                    },
                                                    {
                                                        "high": "\"C1\"",
                                                        "inclusion": 3,
                                                        "low": "\"C1\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    }
                                ]
                            },
                            {
                                "#operator": "IntersectScan",
                                "scans": [
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_timestamp_lower_status_code_class",
                                        "index_id": "43c5d0524bc07061",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "inclusion": 0,
                                                        "low": "\"deleted\""
                                                    },
                                                    {
                                                        "high": "\"C1\"",
                                                        "inclusion": 3,
                                                        "low": "\"C1\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_currency_originalSource_timestamp_lower_status_transaction",
                                        "index_id": "1240c741f21f2520",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "inclusion": 0,
                                                        "low": "\"deleted\""
                                                    },
                                                    {
                                                        "high": "\"GBP\"",
                                                        "inclusion": 3,
                                                        "low": "\"GBP\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    },
                                                    {
                                                        "inclusion": 0
                                                    },
                                                    {
                                                        "high": "\"C1\"",
                                                        "inclusion": 3,
                                                        "low": "\"C1\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_currency_timestamp_lower_status_to_number_transactionTotal_class_code_C1_V2",
                                        "index_id": "a63ad08fa500a310",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    },
                                    {
                                        "#operator": "IndexScan3",
                                        "as": "trs",
                                        "index": "prod_adv_originalSource_currency_timestamp_lower_status_to_number_transactionTotal_class_code_C1",
                                        "index_id": "745b2cfca33bce19",
                                        "index_projection": {
                                            "primary_key": true
                                        },
                                        "keyspace": "production",
                                        "namespace": "default",
                                        "spans": [
                                            {
                                                "exact": true,
                                                "range": [
                                                    {
                                                        "high": "\"S1\"",
                                                        "inclusion": 3,
                                                        "low": "\"S1\""
                                                    },
                                                    {
                                                        "high": "\"GBP\"",
                                                        "inclusion": 3,
                                                        "low": "\"GBP\""
                                                    },
                                                    {
                                                        "high": "\"2022-02-03T22:59:59Z\"",
                                                        "inclusion": 3,
                                                        "low": "\"2021-02-02T23:00:00Z\""
                                                    },
                                                    {
                                                        "inclusion": 0,
                                                        "low": "\"deleted\""
                                                    }
                                                ]
                                            }
                                        ],
                                        "using": "gsi"
                                    }
                                ]
                            }
                        ]
                    },
                    {
                        "#operator": "Fetch",
                        "as": "trs",
                        "keyspace": "production",
                        "namespace": "default"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "(((((((`trs`.`_class`) = \"model.Transaction\") and ((`trs`.`originalSource`) = \"S1\")) and ((`trs`.`code`) = \"C1\")) and ((`trs`.`timestamp`) between \"2021-02-02T23:00:00Z\" and \"2022-02-03T22:59:59Z\")) and (not (lower((`trs`.`status`)) = \"deleted\"))) and ((`trs`.`currency`) = \"GBP\"))"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "(`trs`.`transactionTotal`)"
                                        },
                                        {
                                            "as": "flag",
                                            "expr": "((`trs`.`consumerId`) in `consumerIds`)"
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            }
        },
        {
            "#operator": "Alias",
            "as": "st"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "((`st`.`flag`) = true)"
                    },
                    {
                        "#operator": "InitialGroup",
                        "aggregates": [
                            "count(1)"
                        ],
                        "group_keys": []
                    }
                ]
            }
        },
        {
            "#operator": "IntermediateGroup",
            "aggregates": [
                "count(1)"
            ],
            "group_keys": []
        },
        {
            "#operator": "FinalGroup",
            "aggregates": [
                "count(1)"
            ],
            "group_keys": []
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "expr": "count(1)"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}

Vladlan · February 5, 2022, 7:02pm

@vsr1

And noticed that COUNT(1) is really fast

SELECT COUNT(cmrs)
FROM production AS cmrs
WHERE cmrs._class = "model.Consumer"
    AND cmrs.code = "C1"
    AND cmrs.forename IS VALUED
    AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE

takes 43 seconds when with COUNT(1) it takes 0,72 second

vsr1 · February 5, 2022, 7:23pm

@Vladlan ,

It looks like you creating too many indexes and that making IntersectScan/UnionScans.
I think you need to cleanup those indexes.

Let start generic index and USE INDEX hints.

CREATE INDEX idx_consumers ON `production`(brandCode,
           ifmissingornull((`marketingPreferences`.`email`), false),
           ifmissingornull((`marketingPreferences`.`social`), false),
           ifmissingornull((`marketingPreferences`.`post`), false),
           ifmissingornull((`marketingPreferences`.`telephone`), false),
           ifmissingornull((`marketingPreferences`.`sms`), false),
           `forename`
           ) WHERE `_class` = "com.elc.d2c.consumers.model.Consumer";

CREATE INDEX idx_transactions ON `production`(brandCode, `originalSource`,`currency`,`timestamp`,
                                            LOWER(status), `transactionTotal`, consumerId
           ) WHERE `_class` = "com.elc.d2c.transactions.model.Transaction";

SELECT  AVG(TONUMBER(st.transactionTotal)) AS amount
FROM ( WITH consumerIds AS (SELECT RAW META(cmrs).id
                            FROM production AS cmrs USE INDEX(idx_consumers)
                            WHERE cmrs._class = "com.elc.d2c.consumers.model.Consumer"
                                  AND cmrs.brandCode = "CL"
                                  AND cmrs.forename IS VALUED
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
                                  AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE)
       SELECT trs.transactionTotal, trs.consumerId IN consumerIds AS flag
       FROM production trs USE INDEX(idx_consumers)
       WHERE trs._class = "com.elc.d2c.transactions.model.Transaction"
             AND trs.originalSource = "D2C"
             AND trs.brandCode = "CL"
             AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
             AND LOWER(trs.status) != "deleted"
             AND trs.currency = "GBP") AS st
WHERE st.flag = TRUE;

Vladlan · February 5, 2022, 7:58pm

@vsr1 what do you think about to run those queries one by one with with the most suitable indexes and only after that run this complex query but with indexes “USE INDEX” statement?

vsr1 · February 5, 2022, 8:07pm

Create latest two index and run the query from previous post. If required run WITH clause subquery and see result count and see if it IN works need to use JOIN. (Also do EXPLAIN on WITH and whole query and see)

Latest index should work for all previous queries too. As long _class is same

Vladlan · February 5, 2022, 8:18pm

CREATE INDEX prod_adv_originalSource_currency_timestamp_lower_status_to_numbe1377909695 
ON `production`(`originalSource`,`currency`,`timestamp`,lower(`status`),to_number(`transactionTotal`)) 
WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'))

SELECT COUNT(1)
FROM production trs
WHERE trs._class = "model.Transaction"
    AND trs.originalSource = "S1"
    AND trs.code = "C1"
    AND LOWER(trs.status) != "deleted"
    AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
    AND trs.currency = "GBP"

Took: 926.3ms

CREATE INDEX prod_adv_ifmissingornull_marketingPreferences_all_false_C1
ON `production`(
    ifmissingornull((`marketingPreferences`.`email`), false),
    ifmissingornull((`marketingPreferences`.`social`), false),
    ifmissingornull((`marketingPreferences`.`post`), false),
    ifmissingornull((`marketingPreferences`.`telephone`), false),
    ifmissingornull((`marketingPreferences`.`sms`), false),
    `forename`
) 
WHERE ((`_class` = 'model.Consumer') and (`code` = 'C1'))

SELECT COUNT(1)
FROM production AS cmrs
WHERE cmrs._class = "model.Consumer"
    AND cmrs.code = "C1"
    AND cmrs.forename IS VALUED
    AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
    AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE

Took: 748.9ms

So I used this two indexes in you query within USE INDEX() statement and it didn’t help, performance wise.

vsr1 · February 5, 2022, 8:23pm

The problem here is

WHERE ((`_class` = 'model.Transaction') and (`brand` = 'C1'))
WHERE ((`_class` = 'com.elc.d2c.consumers.model.Consumer') and (`code` = 'C1'))

This looks good.

In complex query


code is Missing in query to pick index. class is different. Index WHERE clause must be present in query pick the index

WHERE cmrs._class = "com.elc.d2c.consumers.model.Consumer"
                                  AND cmrs.brandCode = "CL"



WHERE trs._class = "com.elc.d2c.transactions.model.Transaction"
             AND trs.originalSource = "D2C"
             AND trs.brandCode = "CL"

Vladlan · February 5, 2022, 8:31pm

Sorry, I made a typo.
They are

WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'))
WHERE ((`_class` = 'model.Consumer') and (`code` = 'C1'))

And in complex query:

WHERE cmrs._class = "model.Consumer" AND cmrs.code = "C1"

WHERE trs._class = "model.Transaction"
             AND trs.originalSource = "S1"
             AND trs.code = "C1"

vsr1 · February 5, 2022, 8:32pm

Ok. Now try complex query and see if required use indexes from individual queries.

Vladlan · February 5, 2022, 8:34pm

Yes, I used complex query with required indexes and it didn’t help performance wise(

vsr1 · February 5, 2022, 8:35pm

drop prod_adv_originalSource_currency_timestamp_lower_status_to_numbe1377909695
and create following and retry

CREATE INDEX prod_adv_originalSource_currency_timestamp_lower_status_transactiontotal_consumerid
ON `production`(`originalSource`,`currency`,`timestamp`,lower(`status`),`transactionTotal`, consumerId)
WHERE ((`_class` = 'model.Transaction') and (`code` = 'C1'));

Vladlan · February 5, 2022, 10:23pm

@vsr1 It didn’t help (

vsr1 · February 5, 2022, 10:29pm

post the explain of query

Best will be execute query in Query Work Bench after finished goto Plan Text Tab. paste the content here.

Vladlan · February 6, 2022, 10:42am

@vsr1
Explain for query

SELECT AVG(TONUMBER(st.transactionTotal)) AS amount
FROM (WITH consumerIds AS (
        SELECT RAW META(cmrs).id
        FROM production AS cmrs USE INDEX(prod_adv_ifmissingornull_marketingPreferences_all_false_C1)
        WHERE cmrs._class = "model.Consumer"
            AND cmrs.code = "C1"
            AND cmrs.forename IS VALUED
            AND IFMISSINGORNULL(cmrs.marketingPreferences.email,FALSE) = FALSE
            AND IFMISSINGORNULL(cmrs.marketingPreferences.post,FALSE) = FALSE
            AND IFMISSINGORNULL(cmrs.marketingPreferences.sms,FALSE) = FALSE
            AND IFMISSINGORNULL(cmrs.marketingPreferences.social,FALSE) = FALSE
            AND IFMISSINGORNULL(cmrs.marketingPreferences.telephone,FALSE) = FALSE)
    SELECT trs.transactionTotal,
           trs.consumerId IN consumerIds AS flag
    FROM production trs USE INDEX(prod_adv_os_currency_timestamp_lower_status_transactiontotal_consumerid_c1)
    WHERE trs._class = "model.Transaction"
        AND trs.originalSource = "S1"
        AND trs.code = "C1"
        AND LOWER(trs.status) != "deleted"
        AND trs.timestamp BETWEEN "2021-02-02T23:00:00Z" AND "2022-02-03T22:59:59Z"
        AND trs.currency = "GBP") AS st
WHERE st.flag = TRUE;

Indexes:

CREATE INDEX `prod_adv_ifmissingornull_marketingPreferences_all_false_C1` 
ON `production`(
    ifmissingornull((`marketingPreferences`.`email`), false),
    ifmissingornull((`marketingPreferences`.`social`), false),
    ifmissingornull((`marketingPreferences`.`post`), false),
    ifmissingornull((`marketingPreferences`.`telephone`), false),
    ifmissingornull((`marketingPreferences`.`sms`), false),
   `forename`
) 
WHERE ((`_class` = "model.Consumer") 
and (`code` = "C1")) 

CREATE INDEX `prod_adv_os_currency_timestamp_lower_status_transactiontotal_consumerid_c1` 
ON `production`(`originalSource`,`currency`,`timestamp`,lower(`status`),`transactionTotal`,`consumerId`) 
WHERE ((`_class` = "model.Transaction") and (`code` = "C1"))

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "With",
            "bindings": [
                {
                    "expr": "(select raw (meta(`cmrs`).`id`) from `production` as `cmrs` where (((((((((`cmrs`.`_class`) = \"model.Consumer\") and ((`cmrs`.`code`) = \"C1\")) and ((`cmrs`.`forename`) is valued)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`email`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`post`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`sms`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`social`), false) = false)) and (ifmissingornull(((`cmrs`.`marketingPreferences`).`telephone`), false) = false)))",
                    "static": true,
                    "var": "consumerIds"
                }
            ],
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IndexScan3",
                        "as": "trs",
                        "covers": [
                            "cover ((`trs`.`originalSource`))",
                            "cover ((`trs`.`currency`))",
                            "cover ((`trs`.`timestamp`))",
                            "cover (lower((`trs`.`status`)))",
                            "cover ((`trs`.`transactionTotal`))",
                            "cover ((`trs`.`consumerId`))",
                            "cover ((meta(`trs`).`id`))"
                        ],
                        "filter_covers": {
                            "cover ((`trs`.`_class`))": "model.Transaction",
                            "cover ((`trs`.`code`))": "C1"
                        },
                        "index": "prod_adv_os_currency_timestamp_lower_status_transactiontotal_consumerid_c1",
                        "index_id": "1b62c3b7f23785f3",
                        "index_projection": {
                            "entry_keys": [
                                0,
                                1,
                                2,
                                3,
                                4,
                                5
                            ]
                        },
                        "keyspace": "production",
                        "namespace": "default",
                        "spans": [
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "high": "\"S1\"",
                                        "inclusion": 3,
                                        "low": "\"S1\""
                                    },
                                    {
                                        "high": "\"GBP\"",
                                        "inclusion": 3,
                                        "low": "\"GBP\""
                                    },
                                    {
                                        "high": "\"2022-02-03T22:59:59Z\"",
                                        "inclusion": 3,
                                        "low": "\"2021-02-02T23:00:00Z\""
                                    },
                                    {
                                        "high": "\"deleted\"",
                                        "inclusion": 0,
                                        "low": "null"
                                    }
                                ]
                            },
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "high": "\"S1\"",
                                        "inclusion": 3,
                                        "low": "\"S1\""
                                    },
                                    {
                                        "high": "\"GBP\"",
                                        "inclusion": 3,
                                        "low": "\"GBP\""
                                    },
                                    {
                                        "high": "\"2022-02-03T22:59:59Z\"",
                                        "inclusion": 3,
                                        "low": "\"2021-02-02T23:00:00Z\""
                                    },
                                    {
                                        "inclusion": 0,
                                        "low": "\"deleted\""
                                    }
                                ]
                            }
                        ],
                        "using": "gsi"
                    },
                    {
                        "#operator": "Parallel",
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "Filter",
                                    "condition": "((((((cover ((`trs`.`_class`)) = \"model.Transaction\") and (cover ((`trs`.`originalSource`)) = \"S1\")) and (cover ((`trs`.`code`)) = \"C1\")) and (not (cover (lower((`trs`.`status`))) = \"deleted\"))) and (cover ((`trs`.`timestamp`)) between \"2021-02-02T23:00:00Z\" and \"2022-02-03T22:59:59Z\")) and (cover ((`trs`.`currency`)) = \"GBP\"))"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "cover ((`trs`.`transactionTotal`))"
                                        },
                                        {
                                            "as": "flag",
                                            "expr": "(cover ((`trs`.`consumerId`)) in `consumerIds`)"
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            }
        },
        {
            "#operator": "Alias",
            "as": "st"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "((`st`.`flag`) = true)"
                    },
                    {
                        "#operator": "InitialGroup",
                        "aggregates": [
                            "avg(to_number((`st`.`transactionTotal`)))"
                        ],
                        "group_keys": []
                    }
                ]
            }
        },
        {
            "#operator": "IntermediateGroup",
            "aggregates": [
                "avg(to_number((`st`.`transactionTotal`)))"
            ],
            "group_keys": []
        },
        {
            "#operator": "FinalGroup",
            "aggregates": [
                "avg(to_number((`st`.`transactionTotal`)))"
            ],
            "group_keys": []
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "as": "amount",
                                "expr": "avg(to_number((`st`.`transactionTotal`)))"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}

vsr1 · February 6, 2022, 2:50pm

the plan looks optimal

Vladlan · February 6, 2022, 5:10pm

But it takes too much time(