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?