What happened?

Below is a temporary segment table being created and the query had been running for > 1900 seconds:

The query looks like this

The query is slow because MySQL starts with log_action rather than log_visit:

When forcing the join order using a hint (or when using the join prefix hint to prefer log_visit first), then MySQL chooses the right order:

As a result, the query time improved to 21 seconds. That's a 100x improvement:

There are say 20K actions of type 3 in this case but 100K log_visits for the day so MySQL falsely choose to start with log_action.

This results in very long running queries where Matomo cannot catch up archiving and ultimately reports may not become available.

What should happen?

Matomo should ensure that the query is fast.

How can this be reproduced?

Can provide details internally.
The query in question is but reproducing this would depend on the data structure etc.

INSERT IGNORE INTO logtmpsegmente0edd128f97ef6a6fae19f5307fd5c09 (idvisit) SELECT /* trigger = CronArchive, idSegments = [59] */ distinct log_visit.idvisit as idvisit 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 LEFT JOIN log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction WHERE ( log_visit.visit_last_action_time >= '2023-11-27 00:00:00' AND log_visit.visit_last_action_time <= '2023-12-03 23:59:59' AND log_visit.idsite IN ('1') ) AND ( log_action.type = '3' ) ORDER BY log_visit.idvisit ASC

Matomo major version

Matomo 5

Matomo minor or patch Version

5

PHP version

.

Server operating system

.

What browsers are you seeing the problem on?

No response

Computer operating system

.

Relevant log output

No response

Validations

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.