ACYMailing 6.10.03 Joomla 3.9.18
The admin statistics page times out. I found this in my slow query log:
SELECT us.*, m.name, m.subject, u.email, c.id as campaign_id, c.parent_id, SUM(urlclick.click) as total_click
FROM jom_acym_user_stat AS us LEFT JOIN jom_acym_user AS u ON us.user_id = u.id
INNER JOIN jom_acym_mail AS m ON us.mail_id = m.id LEFT JOIN jom_acym_campaign AS c ON m.id = c.mail_id
LEFT JOIN jom_acym_url_click AS urlclick ON urlclick.user_id = us.user_id AND us.mail_id = urlclick.mail_id
GROUP BY us.mail_id, us.user_id ORDER BY us.send_date DESC, u.email ASC LIMIT 0, 100
The query time was 3 minutes 49 seconds. I tested this query in PhpMyAdmin, yep, it's that slow - it wasn't a condition of the db server that caused that performance. I used EXPLAIN on the query to get this ....
jom_acym_user_stat has just under 300k records. I've run the Config->Security->CheckDatabaseIntegrity.
Am I missing an index? Please advise.