Mysql - Opitmizing a range query with group by
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