MySQL log-queries-not-using-indexes shows these quires what index can I add


#1

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


#2

Hi,

Please confirm that your table look like this:
show create table log_visit
| CREATE TABLE log_visit (

idsite int(10) unsigned NOT NULL,

WHERE
log_visit.idsite in (‘2’)

Try to run a explain plan or event the query with

WHERE
log_visit.idsite =2

This change makes all the diference.
How to change it?

Thank you
JM