Potential Timeout Issues when Date/Time indexes are set up in your database

If your visuals are timing out intermittently, especially with date-based queries, this may be due to inefficient use of database indexes when filtering dates. Even queries that normally run quickly can be affected by this issue.

Common Cause

Using functions like date(date_time) in WHERE clauses can prevent the database from using its indexes efficiently. This forces a full table scan, which can cause timeouts or I/O errors, particularly with large datasets.

Solution

Instead of using date functions in filters, reference the datetime column directly. For example:

Instead of:

WHERE date(date_time) >= '2023-01-01'

Use:

WHERE date_time >= '2023-01-01'

Example Query

Here's an optimized query structure that properly utilizes database indexes:

SELECT 
  hour_of_day, 
  date_time, 
  SUM(value) AS "Total Value"
FROM table
WHERE 1=1
[[ AND date_time >= {{startDate}} ]]
[[ AND date_time <= {{endDate}} ]]
GROUP BY hour_of_day, date_time
ORDER BY SUM(value) DESC
LIMIT 10;