Index design for queries using 2 ranges

Question

I am trying to find out how to design the indexes for my data when my query is using ranges for 2 fields.

expenses_tbl:
idx        date     category      amount
auto-inc   INT       TINYINT      DECIMAL(7,2)
PK

The column category defines the type of expense. Like, entertainment, clothes, education, etc. The other columns are obvious.

One of my query on this table is to find all those instances where for a given date range, the expense has been more than $50. This query will look like:

SELECT date, category, amount 
FROM expenses_tbl
WHERE date > 120101 AND date < 120811 
      AND amount > 50.00;

How do I design the index/secondary index on this table for this particular query.

Assumption: The table is very large (It's not currently, but that gives me a scope to learn).

Answer 1

MySQL generally doesn't support ranges on multiple parts of a compound index. Either it will use the index for the date, or an index for the amount, but not both. It might do an index merge if you had two indexes, one on each, but I'm not sure.

I'd check the EXPLAIN before and after adding these indexes:

CREATE INDEX date_idx ON expenses_tbl (date);
CREATE INDEX amount_idx ON expenses_tbl (amount);

Compound index ranges - http://dev.mysql.com/doc/refman/5.5/en/range-access-multi-part.html

Index Merge - http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html