Sicne the upgrade to version 2.x my archive has crawled to a snails pace. Now taking 5 hours + ro run daily when it used to take 5 mins.
I have today moved it over to a dedicated MySQL server to try to indentify the problem, below is a list of the quires showing in the log as not using indexes. Can anyone help me define the indexes these need to ensure the best performance please.
User@Host: analytics[analytics] @ [172.16.0.16]
Query_time: 0.018855 Lock_time: 0.000083 Rows_sent: 10 Rows_examined: 4812
SET timestamp=1394278601;
SELECT sub.*
FROM (
SELECT
log_visit.*
FROM
piwik_log_visit AS log_visit
WHERE
log_visit.idsite in ('2')
AND log_visit.visit_last_action_time >= '2014-03-07 00:00:00'
ORDER BY
idsite, visit_last_action_time DESC
LIMIT 0, 10
) AS sub
GROUP BY sub.idvisit
ORDER BY sub.visit_last_action_time DESC;
User@Host: analytics[analytics] @ [172.16.0.16]
Query_time: 0.054020 Lock_time: 0.000183 Rows_sent: 525 Rows_examined: 10396
SET timestamp=1394278932;
SELECT
CASE
WHEN counter = 50001 THEN '-1'
ELSE `idaction`
END AS `idaction`
, sum(`13`) AS `13`, `type`
FROM (
SELECT
`idaction`,
CASE
WHEN `type` = 1 AND @counter1 = 50001 THEN 50001
WHEN `type` = 1 THEN @counter1:=@counter1+1
WHEN `type` = 2 AND @counter2 = 50001 THEN 50001
WHEN `type` = 2 THEN @counter2:=@counter2+1
WHEN `type` = 3 AND @counter3 = 50001 THEN 50001
WHEN `type` = 3 THEN @counter3:=@counter3+1
WHEN `type` = 4 AND @counter4 = 50001 THEN 50001
WHEN `type` = 4 THEN @counter4:=@counter4+1
WHEN `type` = 8 AND @counter8 = 50001 THEN 50001
WHEN `type` = 8 THEN @counter8:=@counter8+1
ELSE 0
END
AS counter
, `13`, `type`
FROM
( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4, ( SELECT @counter8:=0 ) initCounter8,
(
SELECT
log_link_visit_action.idaction_name_ref as idaction, log_action.type, log_action.name, count(*) as `12`,
sum(log_link_visit_action.time_spent_ref_action) as `13`
FROM
piwik_log_link_visit_action AS log_link_visit_action
LEFT JOIN piwik_log_action AS log_action ON log_link_visit_action.idaction_name_ref = log_action.idaction
WHERE
log_link_visit_action.server_time >= '2014-03-08 00:00:00'
AND log_link_visit_action.server_time <= '2014-03-08 23:59:59'
AND log_link_visit_action.idsite = '2'
AND log_link_visit_action.time_spent_ref_action > 0
AND log_link_visit_action.idaction_name_ref > 0 AND log_link_visit_action.idaction_event_category IS NULL
GROUP BY
log_link_visit_action.idaction_name_ref, idaction
ORDER BY
`12` DESC, log_action.name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`;
Thanks in advance for any help.
Stephen