Start by profiling what date range of records are typically selected from
the table. If 90% of your queries are against transactions for the current
reporting period and only 10% of queries include records from past periods,
then consider maintaining your historical records in a seperate table with
the same structure while retaining only records for the current period in
the primary table. Perhaps a process at month end can insert into the
historical table and delete from the current table. If needed, both current
and historical records can be queried by implementing a view that unionizes
the two tables.