Subversion Repositories SmartDukaan

-- Covering index for the GRN-pending component of the partner investment sweep.

--

-- The sweep runs this every 2 minutes for every active partner:

--

--   SELECT o.customer_id, SUM(o.total_amount)

--     FROM transaction.`order` o

--     JOIN fofo.fofo_store fs ON fs.id = o.customer_id

--    WHERE o.partner_grn_timestamp IS NULL

--      AND o.refund_timestamp IS NULL

--      AND o.billing_timestamp >= '2017-04-01'

--    GROUP BY o.customer_id;

--

-- Before this index there was NO index on transaction.`order` containing

-- partner_grn_timestamp at all, so the optimiser drove off (customer_id, created_timestamp) and

-- fetched every candidate row to evaluate the filter, then sorted for the GROUP BY

-- ("Using temporary; Using filesort"). Forcing the billing index instead only moved it from

-- 1,461ms to 1,258ms, because none of the existing indexes cover the filter columns.

--

-- Column order: customer_id leads so the join and GROUP BY are satisfied in index order (no

-- filesort); the two IS NULL predicates are equality matches so they precede the billing_timestamp

-- range; total_amount is carried to make the index covering.

--

-- ALGORITHM=INPLACE, LOCK=NONE keeps the table readable and writable throughout (MySQL 5.7).

-- Reversible: DROP INDEX idx_order_grn_pending ON transaction.`order`;

ALTER TABLE transaction.`order`

    ADD INDEX idx_order_grn_pending

        (customer_id, partner_grn_timestamp, refund_timestamp, billing_timestamp, total_amount),

    ALGORITHM=INPLACE, LOCK=NONE;