[Bug] MariaDB: View Visitor Profile very slow due to worse selected index, compared to MySQL 8

[marto55555]

What happened?

Clicking on any "View Visitor Profile" takes ~15 seconds. This happens when the DB is MariaDB 10.11.14.

It seems to be caused by a slow query due to an incorrectly selected index.
Sample slow query:

SELECT /* Live.queryAdjacentVisitorId */ sub.idvisitor, sub.visit_last_action_time FROM (
	SELECT
			log_visit.idvisitor, MAX(log_visit.visit_last_action_time) as visit_last_action_time
	FROM
			mt_log_visit AS log_visit
	WHERE
			log_visit.idsite = '4' AND log_visit.idvisitor <> 'test' AND visit_last_action_time >= '2026-02-28 10:18:10' and visit_last_action_time <= '2026-03-02 10:18:10'
	GROUP BY
			log_visit.idvisitor
	ORDER BY
			MAX(log_visit.visit_last_action_time) ASC) as sub
WHERE sub.visit_last_action_time >= '2026-03-01 10:18:10'
LIMIT 1;

MariaDB 10.11.14 EXPLAIN:

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ALL",
          "rows": 17592,
          "filtered": 100,
          "attached_condition": "sub.visit_last_action_time >= '2026-03-01 10:18:10'",
          "materialized": {
            "query_block": {
              "select_id": 2,
              "having_condition": "visit_last_action_time >= '2026-03-01 10:18:10'",
              "filesort": {
                "sort_key": "max(log_visit.visit_last_action_time)",
                "temporary_table": {
                  "nested_loop": [
                    {
                      "table": {
                        "table_name": "log_visit",
                        "access_type": "range",
                        "possible_keys": [
                          "index_idsite_config_datetime",
                          "index_idsite_datetime",
                          "index_idsite_idvisitor_time"
                        ],
                        "key": "index_idsite_idvisitor_time",
                        "key_length": "4",
                        "used_key_parts": ["idsite"],
                        "rows": 5077800,
                        "filtered": 0.173224613,
                        "attached_condition": "log_visit.idsite = '4' and log_visit.idvisitor <> 'test' and log_visit.visit_last_action_time >= '2026-02-28 10:18:10' and log_visit.visit_last_action_time <= '2026-03-02 10:18:10'",
                        "using_index": true
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  }
}

MySQL 8 EXPLAIN:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1487.72"
    },
    "table": {
      "table_name": "sub",
      "access_type": "ALL",
      "rows_examined_per_scan": 13202,
      "rows_produced_per_join": 13202,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "167.53",
        "eval_cost": "1320.20",
        "prefix_cost": "1487.73",
        "data_read_per_join": "309K"
      },
      "used_columns": [
        "idvisitor",
        "visit_last_action_time"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "15475.91"
          },
          "ordering_operation": {
            "using_filesort": true,
            "grouping_operation": {
              "using_temporary_table": true,
              "using_filesort": false,
              "table": {
                "table_name": "log_visit",
                "access_type": "range",
                "possible_keys": [
                  "index_idsite_config_datetime",
                  "index_idsite_datetime",
                  "index_idsite_idvisitor_time"
                ],
                "key": "index_idsite_datetime",
                "used_key_parts": [
                  "idsite",
                  "visit_last_action_time"
                ],
                "key_length": "9",
                "rows_examined_per_scan": 14670,
                "rows_produced_per_join": 13202,
                "filtered": "90.00",
                "index_condition": "((`matomo`.`log_visit`.`idsite` = 4) and (`matomo`.`log_visit`.`visit_last_action_time` >= TIMESTAMP'2026-02-28 10:18:10') and (`matomo`.`log_visit`.`visit_last_action_time` <= TIMESTAMP'2026-03-02 10:18:10'))",
                "cost_info": {
                  "read_cost": "14155.61",
                  "eval_cost": "1320.30",
                  "prefix_cost": "15475.91",
                  "data_read_per_join": "310M"
                },
                "used_columns": [
                  "idvisit",
                  "idsite",
                  "idvisitor",
                  "visit_last_action_time"
                ],
                "attached_condition": "(`matomo`.`log_visit`.`idvisitor` <> 'test')"
              }
            }
          }
        }
      }
    }
  }
}

What should happen?

It should take <1 second, like when the DB is MySQL 8, with the same data as in the MariaDB case.

With MariaDB, I can make the query fast by adding FORCE INDEX (index_idsite_datetime) after the FROM clause. So, this seems like a potential fix?

How can this be reproduced?

Click on any "View Visitor Profile".

Matomo version

5.7.1

PHP version

8.5.3

Server operating system

Ubuntu 24.04

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

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.

[marto55555]

With MariaDB, I can make the query fast by adding FORCE INDEX (index_idsite_datetime) after the FROM clause. So, this seems like a potential fix?