Appendix / Advanced Report Usage Notes |
Filtering on computed values in most cases prevents MySQL from being able to take advantage of indexes. For example, the following will force a scan of every record in TBL_TRANSFER_SESSIONS, because MySQL has to perform the CONVERT( ) on ts.started_at for every record:
SELECT DISTINCT ts.* FROM $TBL_TRANSFER_SESSIONS ts WHERE CONVERT(ts.started_at, DATE) = DATE(NOW()) ;
Instead, compute the correct criteria to compare the raw value against:
SET @todays_date = DATE(NOW()); SET @tomorrows_date = DATE_ADD(@todays_date, INTERVAL 1 DAY); SELECT DISTINCT ts.* FROM $TBL_TRANSFER_SESSIONS ts WHERE ts.started_at >= @todays_date AND ts.started_at < @tomorrows_date ;
The builtin report variables $REPORT_PERIOD_START and $REPORT_PERIOD_END contain datetimes converted from local time zone of input into UTC and are usually a better choice for date filtering (unless recipient is fine with UTC-based filtering).