Search code examples
mysqlindexingdate-comparison

What indexes would be useful in this query?

I am doing some queries on a large number of records between two tables. The tables only key are the auto-incremental id's.

    SELECT 
    discounts.id,
    vendor_number,
    discounts.sku,
    deal_number,
    deal_start_date,
    deal_end_date,
    sales.cost,
    sales.quantity,
    sales.invoice_date
FROM
    discounts
        INNER JOIN
    sales ON discounts.sku = sales.sku
WHERE
    invoice_date >= deal_start_date
        AND invoice_date <= deal_end_date

The work of the query is the date comparisons I am sure, but I am not sure what kind of index to setup to help this query perform better.

Solution

  • The where condition is really part of the join (assuming that deal_start_date and deal_end_date are from the discounts table). If so, this is just a join optimization. The best index would have the columns used in the join, sku first (because of the equality). So, I think these would be good indexes:

    sales(sku, invoice_date)
discounts(sku, deal_start_date, deal_end_date)