Re: dynamic events categorization

On Thu, Jun 26, 2008 at 05:06:14PM +0200, Marc Mamin wrote:
> 
> Hello,

Hi Marc,

> I guess that the time offsets (now-21 and now-28) are evaluated each
> time the corresponding condition is met.

Excellent suggestion, this makes the query ~ 15% faster. Every bit
counts.

> It may be faster to put them into a separate sub query. I'm not sure
> about putting "now" itself within the sub query...

Where would you put it?

> It may also be better to put your query in a procedure where you can put
> these constants into variables instead of using a sub query.
> 
> Depending of the distribution of a) 2_past,1_future,0_current and '' and
> b) t.type,  it may be worth to have different queries, bound with UNION
> ALL. This would simplify the "CASE" construct and at least part of the
> tests should happen on indexes only.

Could you give a very short example?

> If the query is run very often, you may want to add a boolean column
> is_past on show_date, and have a separate job that put the concerned
> records to true every x minutes ...

That would require a cron job (?). I'm trying to keep the app self-contained
for now.

> HTH,

It sure does, thanks!

Cheers,