[Bug] Slow archiving query because of MySQL optimizer making wrong decision

[tsteur]

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.

[tsteur]

Using eg /*+ JOIN_PREFIX ($firstMainTable) */ ( example: /*+ JOIN_PREFIX (log_visit) */) worked for me. Not only in the explain but also when executing the SELECT query directly and it also worked for me as part of the INSERT SELECT INTO.
Using JOIN_PREFIX has an advantage that we don't super strictly tell MySQL the table order but which one to start with.

I haven't checked but this may require MySQL 8+

[michalkleiner]

Would the hint be ignored by older versions so it technically doesn't matter if it's there always?
Do you think it's always the first table but could it make it worse in some cases and we'd need some more logic to hint at the other tables at other times e.g. based on number of rows?

[tsteur]

It could. If we put it behind a config flag, then we could easily enable/disable on demand though which maybe lowers risk?