List:General Discussion« Previous MessageNext Message »
From:Simon J Mudd Date:July 13 2009 8:37pm
Subject:Re: Need advice on a good setup for "generic queries"
View as plain text  
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.

Simon
Thread
Need advice on a good setup for "generic queries"Morten12 Jul
  • Re: Need advice on a good setup for "generic queries"mos12 Jul
    • Re: Need advice on a good setup for "generic queries"Morten13 Jul
      • Re: Need advice on a good setup for "generic queries"mos13 Jul
        • Re: Need advice on a good setup for "generic queries"Morten13 Jul
        • Re: Need advice on a good setup for "generic queries"Simon J Mudd13 Jul