How do I improve the load time of the find visitor SQL query when visitors have many visits?

You may notice a performance problem that a MySQL query like the one below causes a lot of load on your database. This can happen when a visitor has thousands of visits.

SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_seconds_since_first, visitor_seconds_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, idsite, profilable, visit_entry_idaction_url, visit_total_actions, visit_total_interactions, visit_total_searches, referer_url, config_browser_name, config_client_type, config_device_brand, config_device_model, config_device_type, visit_total_events, visit_total_time, location_ip, location_browser_lang, campaign_content, campaign_group, campaign_id, campaign_keyword, campaign_medium, campaign_name, campaign_placement, campaign_source, last_idlink_va, custom_dimension_1, custom_dimension_2, custom_dimension_3, custom_dimension_4, custom_dimension_5, custom_dimension_6, 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 
            FROM log_visit FORCE INDEX (index_idsite_idvisitor)  
            WHERE idsite = '1' AND visit_last_action_time <= '2020-12-07 20:34:48' AND idvisitor = '...'
            ORDER BY visit_last_action_time DESC
            LIMIT 1

If you are using the User ID feature feature, then a solution to fix this performance issue can be to adjust your config/config.ini.php and set below configuration option. Learn more about what this option does in How are requests with a User ID tracked?.

[Tracker]
enable_userid_overwrites_visitorid=0

If the User ID feature is not used or if you don’t want to change above configuration, it may help to adjust a database index using the two MySQL queries below (where the matomo_ DB table prefix needs to be adjusted depending on the configuration.). Please note that these queries can take a long time to execute and can slow down your Matomo while they aren’t finished. On a high traffic Matomo installation you may need to temporarily disable tracking until these two queries are finished.

DROP INDEX `index_idsite_idvisitor` ON matomo_log_visit;
CREATE INDEX `index_idsite_idvisitor` ON matomo_log_visit (idsite, idvisitor, visit_last_action_time DESC)