@anonymous-piwik-user opened this issue on March 18th 2008

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,
                                                        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;

@anonymous-piwik-user commented on March 18th 2008

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);
@anonymous-piwik-user commented on March 18th 2008

I add index on tables, and yes it is day and night ! So add index is the good solution

@anonymous-piwik-user commented on March 25th 2008

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.

@mattab commented on March 28th 2008

(In [421]) - refs #5570 adding index on tables. Thanks for the suggestion

This issue was closed on March 28th 2008
Powered by GitHub Issue Mirror