Slow Query in 1.7.1 on large deployment

Support & Bugs
1

This query shows up over and over in the mysql slow query log (dominant thing I see):


( SELECT        idvisitor,
                                                        visit_last_action_time,
                                                        visit_first_action_time,
                                                        idvisit,
                                                        visit_exit_idaction_url,
                                                        visit_exit_idaction_name,
                                                        visitor_returning,
                                                        visitor_days_since_first,
                                                        visitor_days_since_order,
                                                        location_country,
                                                        location_continent,
                                                        referer_name,
                                                        referer_keyword,
                                                        referer_type,
                                                        visitor_count_visits,
                                                        visit_goal_buyer
                                                        ,
                                custom_var_k1, custom_var_v1,
                                custom_var_k2, custom_var_v2,
                                custom_var_k3, custom_var_v3,
                                custom_var_k4, custom_var_v4,
                                custom_var_k5, custom_var_v5
                 ,
                                        0 as priority
                                        FROM piwik_log_visit
                                        WHERE visit_last_action_time >= '2012-03-11 14:30:40' AND idsite = '1'  AND config_id = 'b91»Ç~B°¡'
                                        ORDER BY visit_last_action_time DESC
                                        LIMIT 1
                         )
                                        UNION
                                        ( SELECT        idvisitor,
                                                        visit_last_action_time,
                                                        visit_first_action_time,
                                                        idvisit,
                                                        visit_exit_idaction_url,
                                                        visit_exit_idaction_name,
                                                        visitor_returning,
                                                        visitor_days_since_first,
                                                        visitor_days_since_order,
                                                        location_country,
                                                        location_continent,
                                                        referer_name,
                                                        referer_keyword,
                                                        referer_type,
                                                        visitor_count_visits,
                                                        visit_goal_buyer
                                                        ,
                                custom_var_k1, custom_var_v1,
                                custom_var_k2, custom_var_v2,
                                custom_var_k3, custom_var_v3,
                                custom_var_k4, custom_var_v4,
                                custom_var_k5, custom_var_v5
                 ,
                                        1 as priority
                                        FROM piwik_log_visit
                                        WHERE visit_last_action_time >= '2012-03-11 14:30:40' AND idsite = '1'  AND idvisitor = 'W~Sn~\P~T^EÅ'
                                        LIMIT 1
                         )
                                        ORDER BY priority DESC
                                        LIMIT 1;

I get about 230 thousand page views a day so we are fairly active; maybe this is unique to large deployments? Any help?

2

Explain output:


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	piwik_log_visit 	range 	index_idsite_config_datetime,index_idsite_datetime... 	index_idsite_config_datetime 	20 	NULL	1 	Using where
2 	UNION 	piwik_log_visit 	ref 	index_idsite_config_datetime,index_idsite_datetime... 	index_idsite_idvisitor 	12 	const,const 	1 	Using where
NULL	UNION RESULT 	<union1,2> 	ALL 	NULL	NULL	NULL	NULL	NULL	Using filesort
3

Thanksfor the report. We have not received similar complaitnts, but it’s possible.

Do you use “Delete logs older than N days” feature? it might help keep DB size lower and faster queries.
Otherwise, run a SHOW FULL PROCESSLIST; when the query slow is happening, to see if other queries are running (eg. backup process slowinig down, etc.)

4

I have the system set to delete logs over 30 days, every week. No other major cron jobs are set to run when the queries show up in the logs. They are not causing major slow downs but do stick up above the noise… Maybe some optimization can be done here. This is one BIG query!!