mos99@stripped (mos) writes:
> At 08:06 PM 7/12/2009, Morten wrote:
> If you can get rid of the DateTime and switch to just Date it may
> speed up the indexes.
While not as pretty it's more compact to convert timestamp values into
an bigint. For example: seconds since epoch. If you know the ranges
to put in the query then store them this way and thus save on some
storage, and therefore improve performance. May be worth considering?
> >These queries which involve easily indexable fields (status_id,
> >assignee_id, company_id) and multiple conditions on different ranges
> >are what's difficult. The table is about 2.500.000 records and grows
> >at a daily rate of about 50.000 records (that number is growing
> >though). Once an action has been closed, it gets status "closed" and
> >is no longer of interest. 70% of the records in the table will be
> >status "closed".
As mentioned if you are not interested in "closed" queries get rid of them.
put them in another table.
That reduces the number of rows and hence the query time.