Actually our DB is composed by 1197 events, all single day, but with 
repetition each year for 100 repetitions (it contains birthdays and 
"famous" events). The database has been made in this way because we 
imported a different db we had previously. Of course, a first fix, 
should be a fix to the db that can be... "shorter".

My fix is just checking that the query is done for just one specific 
day, and in that case I just replicated the function that check if the 
events are "onDate" (iCalEvent::eventOnDate).

The base query was resulting about 4000 events that your eventOnDate had 
to parse, hitting on a dev server about 1,2 seconds to process all the 
records. The addition I made to the query, filter directly in MySQL the 
events, resulting 4/5 records per day (as expected): parsing is now 0,05 
seconds long.

I pull a request of fix, but of course I'm not sure how is going to 
process "multikind" events. Working directly with database can greatly 
improve the application performance.



On 21/01/14 12:38, Geraint Edwards wrote:
>
> In component/site/libraries/dbmodel.php:
>
> > @@ -1876,7 +1876,18 @@ function listIcalEvents($startdate, $enddate, $order = "", $filters = false, $ex
> >  			$startdate = JevDate::strftime('%Y-%m-%d 00:00:00', $startdate);
> >  			$enddate = JevDate::strftime('%Y-%m-%d 23:59:59', $enddate);
> >  		}
> > -
> > +
> > +        // Improve the where for the single day event pages to speed up the query
> > +        // Filter is taken from the iCalEvent::eventOnDate but for a db with a lot
> > +        // of events (> 2000 with repetition for 5 years) this had been a quick fixes.
> > +        // Can be better done.
> > +        $startday = explode(" ", $startdate);
> > +        $endday = explode(" ", $enddate);
> > +        $justToday = false;
> > +        if ($startday[0] == $endday[0]) {
> > +            $justToday = " AND multiday=0 AND '$startdate' < DATE_ADD(rpt.startrepeat, INTERVAL 1 DAY)";
>
> Thanks for the contribution - it had me thinking for a while as to why 
> it should improve performance. We are always desperate for 
> improvements in database query performance so every idea is very welcome.
>
> The idea of this restriction is to not show events that started more 
> than 24 hours ago if they are configured not to appear on second or 
> subsequent days. I'm interested that this should have much impact on 
> the query speed - I wonder if you can tell me a little about the types 
> of events you have on your site - e.g. mix of single and multi-day 
> events? etc.
>
> Strictly speaking we should be testing for rpt.startrepeat before 
> midnight as opposed to 24 hours before the startdate and also we 
> should have an OR statement in there too to handle the multi-day 
> events that should be appearing. Adding the OR is likely to make the 
> query a lot slower I suspect.
>
> —
> Reply to this email directly or view it on GitHub 
> <https://github.com/JEvents/JEvents/pull/157/files#r9030224>.
>