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;