1

I have a table with the following definition:

CREATE TABLE `sales` (
  `saleId` varchar(100) NOT NULL,
  `orderId` varchar(100) DEFAULT NULL,
  `shopId` varchar(100) DEFAULT NULL,
  `fulfillableQuantity` int(11) DEFAULT NULL,
  `fulfillmentStatus` varchar(30) DEFAULT NULL,
  `grams` double DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `productExists` tinyint(1) DEFAULT NULL,
  `productId` varchar(100) DEFAULT NULL,
  `variantId` varchar(100) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `requiresShipping` tinyint(1) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `taxable` tinyint(1) DEFAULT NULL,
  `title` text,
  `totalDiscounts` double DEFAULT NULL,
  `variantTitle` text,
  `cogs` double DEFAULT NULL,
  `customerEmail` varchar(100) DEFAULT NULL,
  `discount` double DEFAULT NULL,
  `processedAt` varchar(100) DEFAULT NULL,
  `shippingCost` double DEFAULT NULL,
  `rowStatus` varchar(10) DEFAULT NULL,
  `processedAtNano` bigint(20) DEFAULT NULL,
  `lastUpdated` bigint(20) DEFAULT NULL,
  `productType` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`saleId`),
  KEY `i_shopId_nano` (`shopId`,`processedAtNano`),
  KEY `i_sales_orderid` (`orderId`),
  KEY `i_sales_variantid` (`variantId`),
  KEY `date_and_product_index` (`shopId`,`processedAtNano`,`productId`,`rowStatus`),
  KEY `i_sales_custemail` (`customerEmail`) USING BTREE,
  KEY `i_sales_test_index` (`shopId`,`processedAtNano`,`rowStatus`,`customerEmail`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

and I'm trying to run this query:

SELECT
    get_total_freq(MAX(s.processedAtNano),
        MIN(s.processedAtNano),
        count(s.customerEmail)) AS a
FROM
    sales AS s
WHERE
    s.shopId = 'p2k-c5k-o100k'
    AND s.processedAtNano < 1525132799999
    AND s.customerEmail IS NOT NULL
    AND s.customerEmail != ''
    AND s.rowStatus = 'ok'
GROUP BY
    s.customerEmail
HAVING
    count(s.customerEmail) > 1)

but this just won't use the indexes and would start using where and filesort

below is the explain for this query:

1|"SIMPLE"|"s"|"ref"|"i_shopId_nano,date_and_product_index,i_sales_test_index"|"i_shopId_nano"|"103"|"const"|277686|"Using index condition; Using where; Using temporary; Using filesort"|

I have experimented with all logical permutations of indexes that I can think of but still, the query time is 11sec, while without the group by its 630ms

definition of get_total_freq:

CREATE FUNCTION `get_total_freq`(max BIGINT, min BIGINT, no_of_orders INT) RETURNS double
begin
    declare value double;
    set value = DATEDIFF( from_unixtime(floor(max/1000)), 
from_unixtime(floor(min/1000)) )/ (no_of_orders-1);
    RETURN value;
end

UPDATE

Solution I ended up using thanks to both Rick James and jonggu as it was a mix of both

The final index that I created

"i_sales_stack" => "shopId,rowStatus,customerEmail,processedAtNano"

And the final query I'm using

SELECT
    avg(b.total_freq)
FROM
    sales AS s, (
        SELECT
            saleId,
            get_total_reorder_freq (MAX(a.processedAtNano),
                MIN(a.processedAtNano),
                count(a.customerEmail)) AS total_freq
        FROM
            sales AS a
        WHERE
            a.shopId = 'p2k-c5k-o100k'
            AND a.rowStatus = 'ok'
        GROUP BY
            a.customerEmail
        HAVING
            count(a.customerEmail) > 1) AS b
    WHERE
        s.saleId = b.saleId
        AND s.processedAtNano < 1531440000000
        AND s.customerEmail != ''
1
  WHERE s.shopId = 'p2k-c5k-o100k'
    AND s.processedAtNano < 1525132799999
    AND s.customerEmail IS NOT NULL   -- remove
    AND s.customerEmail != ''
    AND s.rowStatus = 'ok'

First, let's look at the two tests for customerEmail. NULL makes any test fail, so customerEmail != '' already covers the IS NOT NULL test.

To build the optimal index, start with = tests.

INDEX(shopId, rowStatus,   -- first, in either order
      customerEmail,       -- not sure if it can get pat this
      processedAtNano)     -- range last

The GROUP BY cannot be subsumed by any INDEX because no index can effectively handle the entire WHERE. Hence the filesort is inevitable.

It does not make sense to GROUP BY customerEmail without having that column in the SELECT.

What is get_total_freq()?

Meanwhile i_shopId_nano is unnecessary because of i_sales_test_index.

The difference between count(s.customerEmail) and COUNT(*) is that the former tests for NULL. But you have excluded such.

  • Have added GROUP By customerEmail cause the we have to get the get_total_freq by every customer who has purchased before 1525132799999. I have read that the index are always used if the follow the order of where clause, is this false? – Ankit Ladhania Jul 12 '18 at 16:51
  • Have added the definition of get_total_freq in the question – Ankit Ladhania Jul 12 '18 at 16:52
  • @AnkitLadhania - Rearranging the order of tests in WHERE has no impact on the Optimization. Rearranging the order of columns in an INDEX may have a serious impact. – Rick James Jul 12 '18 at 19:03
  • @AnkitLadhania - And... An index is used if the Optimizer decides that it would speed up the query. That decision is based on various things. – Rick James Jul 12 '18 at 19:05
  • The test is really for "purchases before 15..." And you are excluding customers who purchased only one item before that date. – Rick James Jul 12 '18 at 19:10
1

Try this.

Change index like.

KEY i_sales_test_index (shopId,rowStatus,customerEmail)

SELECT
    1 AS sortKey,
    avg(avgs.a) AS reorder_freq
FROM (
    SELECT
        b.total_freq AS a
    FROM
        sales AS s, (
            SELECT
                saleId,
                get_total_freq (MAX(a.processedAtNano),
                    MIN(a.processedAtNano),
                    count(a.customerEmail)) AS total_freq
            FROM
                sales AS a
            WHERE
                a.shopId = 'p2k-c5k-o100k'
                AND a.rowStatus = 'ok'
            GROUP BY
                a.customerEmail
            HAVING
                count(a.customerEmail) > 1) AS b
        WHERE
            s.saleId = b.saleId
            AND s.processedAtNano < 1525132799999
            AND s.customerEmail IS NOT NULL
            AND s.customerEmail != '') AS avgs
  • Can you please explain your logic behind this optimization? – Ankit Ladhania Jul 12 '18 at 4:57
  • @AnkitLadhania My query is doing self join. Because index doesn't apply after range search. – jonggu Jul 12 '18 at 5:05
  • Thanks your solution helped, so just for my understanding, MySQL doesn't use index after range queries? – Ankit Ladhania Jul 12 '18 at 5:10
  • @AnkitLadhania Yes. MySQL doesn't use index after range condition for composite index – jonggu Jul 12 '18 at 5:15
  • it did speed up the query from previous one but it was not what I ended up using, please see the index that Rick James suggested that is more optimized – Ankit Ladhania Jul 13 '18 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.