Mysql - Opitmizing a range query with group by 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 &lt; 1525132799999
    AND s.customerEmail IS NOT NULL
    AND s.customerEmail != ''
    AND s.rowStatus = 'ok'
GROUP BY
    s.customerEmail
HAVING
    count(s.customerEmail) &gt; 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" =&gt; "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) &gt; 1) AS b
    WHERE
        s.saleId = b.saleId
        AND s.processedAtNano &lt; 1531440000000
        AND s.customerEmail != ''
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) &gt; 1) AS b
        WHERE
            s.saleId = b.saleId
            AND s.processedAtNano &lt; 1525132799999
            AND s.customerEmail IS NOT NULL
            AND s.customerEmail != '') AS avgs