Slow query when 2000 pages view a day

[anonymous-matomo-user]

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 :

```

1. 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;

```

[anonymous-matomo-user]

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-matomo-user]

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

[anonymous-matomo-user]

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]

(In ⁴²¹) – refs #5570 adding index on tables. Thanks for the suggestion