nb_uniq_visitors query locks mysql in "sending data" state

Support & Bugs
1

Hi everybody,

My piwik_log_visit table has 1,276,576 records, MyISAM engine, utf8_general_ci collation and is 307.0 MiB in size.
Recently PHP started to hang, and I identified the problem in this Piwik query, hanging forever in “Sending data” state:

SELECT count(distinct idvisitor) as nb_uniq_visitors FROM piwik_log_visit AS log_visit WHERE visit_last_action_time >= ‘2011-07-31 22:00:00’ AND visit_last_action_time <= ‘2011-08-31 21:59:59’ AND idsite = ‘14’

Yesterday I did a “repair table piwik_log_visits”. Then the query took a few seconds to run after the repair.
Now today the same query takes 3 minutes to finish.

Does anybody know how can I fix this problem?

Thanks.
VittGam

2

WE could add a setting to “disable” the unique visitors feature, but in the meantime, please setup cron script as explained in How to Set up Auto-Archiving of Your Reports - Analytics Platform - Matomo