Archive Heavy Query


#1

Hi all,
I’ve several queries like this that invalidates the usage of partitionig by month. Is this correct?
(Why compare with last hour of the last month? Bug?)

log_visit.visit_last_action_time >= '2014-09-30 23:00:00’
AND log_visit.visit_last_action_time <= ‘2014-10-31 23:59:59’

Instead I’ts possible to have a query like this?

log_visit.visit_last_action_time>= '2014-10-01’
AND log_visit.visit_last_action_time < ‘2014-10-01’ + INTERVAL 1 MONTH

Thank you

Regards
Jorge M.

SELECT
count(distinct log_inner.idvisitor) AS 1,
count(distinct log_inner.user_id) AS 39
FROM
(
SELECT
log_visit.idvisitor,
log_visit.user_id
FROM
log_visit AS log_visit
LEFT JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
WHERE
( log_visit.visit_last_action_time >= ‘2014-09-30 23:00:00’
AND log_visit.visit_last_action_time <= ‘2014-10-31 23:59:59’
AND log_visit.idsite IN (‘9’) )
AND
( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM log_action WHERE ( name LIKE CONCAT(’%’, ‘site/life/’, ‘%’) AND type = 1 )) ) )
GROUP BY log_visit.idvisit
) AS log_inner


(Matthieu Aubry) #2

Would the change you suggest make the query faster, or how would it improve?