Problem Description:

The SQL query that counts log entries per language resource is running slowly.

The current execution plan shows:

• Full table scan over ~570k rows (type = ALL).

• No index selected, although languageResourceId and created indexes exist.

• GROUP BY languageResourceId triggers Using temporary; Using filesort.

• Filtering combines multiple conditions (languageResourceId IN (…), level <= 8, created >= NOW() - INTERVAL 2 month), which prevents efficient use of the existing single-column indexes.

Impact:

• High query times when generating log statistics.

• Increased database load during reporting.

Notes:

• A composite index covering (languageResourceId, created, level) would allow the optimizer to apply filters and grouping more efficiently.

• Alternatively, (created, languageResourceId) could help if languageResourceId lists are very large.

• Query rewrite (splitting large IN (…) lists, or pre-aggregating logs) might further improve performance if indexes alone are insufficient.

Next Steps:

• Add and test a composite index on LEK_languageresources_log.

• Benchmark with real production data.

• Revisit query structure if performance remains an issue.

Solution

The following index reduces the scanned rows to 3200 rows: 

CREATE INDEX IF NOT EXISTS idx_langres_log_composite
ON LEK_languageresources_log (languageResourceId, created, level);