Re: Seemingly erratic behavior

[Peter Brawley]

On these fora, use BBCode code tags to preserve text formatting.

Five left joins and much IFing and ORing. It's going to be slow.

The behaviour you describe suggests you're running out of memory so it's having to swap, and it's thrashing. Run Explain Extended on it and multiply the row counts to see just how many table rows it has to read. Post the Explain result and the result of Show Create Table for each table.

Have you proved that you need all that IFing and ORing and JOINing just to get one set of counts?

Some simplifications would likely speed it up if the right covering indexes are in place, eg if you write ...

set @today = unix_timestamp(curdate(), ' 23:59:59') );

then ...

IF( FROM_UNIXTIME(p.start_date) > concat(CURDATE( ) , ' 23:59:59'),

...becomes...

IF( p.start_date > @today,

which the optimiser can work with.