Each time I take a look at one of my site (who log 2000 pages view a day) I get a mysql slow query, that is a big problem because 2000 pages a day is very small indeed.
This is the query :
# Query_time: 21 Lock_time: 0 Rows_sent: 268 Rows_examined: 7195655 SELECT name, type, count(distinct t1.idvisit) as nb_visits, count(distinct visitor_idcookie) as nb_uniq_visitors, count(*) as nb_hits FROM (piwik_log_visit as t1 LEFT JOIN piwik_log_link_visit_action as t2 USING (idvisit)) LEFT JOIN piwik_log_action as t3 USING (idaction) WHERE visit_server_date = '2008-03-17' AND idsite = '2' GROUP BY t3.idaction;
There are currently no indexes on the log tables. I tried to add a few and that made all the difference for me:
CREATE INDEX index_idvisit ON log_link_visit_action (idvisit); CREATE INDEX index_idaction ON log_action (idaction); CREATE INDEX index_idsite ON log_visit (idsite); CREATE INDEX index_visit_server_date ON log_visit (visit_server_date);
I add index on tables, and yes it is day and night !
So add index is the good solution
If it could be useful I confirm this as a working solution.
In my monitored sites (1500~2000 visits per day) I have no more slow queries.
(In ) - refs #5570 adding index on tables. Thanks for the suggestion