[Dev 2431] Enrich log_visit table index by a third column (#18636)

* Update index for new installs

* Add Update script for 5.0.0-b1 which holds the log_visits table index update.

* Update class comment

* Check that the correct log_visit index exists if one is found

* Refactor update script to use migration class

* Utilise getMigrations method from base class

* Remove duplicate check

2 files changed, 92 insertions, 1 deletions

diff --git a/core/Db/Schema/Mysql.php b/core/Db/Schema/Mysql.php
index 09b0b48922..7421b02ee9 100644
--- a/core/Db/Schema/Mysql.php
+++ b/core/Db/Schema/Mysql.php
@@ -191,7 +191,7 @@ class Mysql implements SchemaInterface
                                 PRIMARY KEY(idvisit),
                                 INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time),
                                 INDEX index_idsite_datetime (idsite, visit_last_action_time),
-                                INDEX index_idsite_idvisitor (idsite, idvisitor)
+                                INDEX index_idsite_idvisitor (idsite, idvisitor, visit_last_action_time DESC)
                               ) ENGINE=$engine DEFAULT CHARSET=$charset
             ",
 
diff --git a/core/Updates/5.0.0-b1.php b/core/Updates/5.0.0-b1.php
new file mode 100644
index 0000000000..8837881321
--- /dev/null
+++ b/core/Updates/5.0.0-b1.php
@@ -0,0 +1,91 @@
+<?php
+
+/**
+ * Matomo - free/libre analytics platform
+ *
+ * @link    https://matomo.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ *
+ */
+
+namespace Piwik\Updates;
+
+use Piwik\Db;
+use Piwik\Common;
+use Piwik\Updater;
+use Piwik\Updater\Migration\Db as DbAlias;
+use Piwik\Updater\Migration\Factory;
+use Piwik\Updates as PiwikUpdates;
+
+/**
+ * Update for version 5.0.0-b1
+ */
+class Updates_5_0_0_b1 extends PiwikUpdates
+{
+    /**
+     * @var Factory
+     */
+    private $migration;
+    private $tableName;
+    private $indexName;
+
+    public function __construct(Factory $factory)
+    {
+        $this->migration = $factory;
+
+        $this->tableName = Common::prefixTable('log_visit');
+        $this->indexName = 'index_idsite_idvisitor';
+    }
+
+    public function doUpdate(Updater $updater)
+    {
+        $updater->executeMigrations(__FILE__, $this->getMigrations($updater));
+    }
+
+    public function getMigrations(Updater $updater)
+    {
+        if ($this->requiresUpdatedLogVisitTableIndex()) {
+            return $this->getLogVisitTableMigrations();
+        }
+
+        return [];
+    }
+
+    private function getLogVisitTableMigrations()
+    {
+        $migrations = [];
+
+        $migrations[] = $this->migration->db->dropIndex('log_visit', $this->indexName);
+
+        // Using the custom `sql` method instead of the `addIndex` method as it doesn't support DESC collation
+        $migrations[] = $this->migration->db->sql(
+            "ALTER TABLE `{$this->tableName}` ADD INDEX `{$this->indexName}` (`idsite`, `idvisitor`, `visit_last_action_time` DESC)",
+            [DbAlias::ERROR_CODE_DUPLICATE_KEY, DbAlias::ERROR_CODE_KEY_COLUMN_NOT_EXISTS]
+        );
+
+        return $migrations;
+    }
+
+    private function requiresUpdatedLogVisitTableIndex()
+    {
+        $sql = "SHOW INDEX FROM `{$this->tableName}` WHERE Key_name = '{$this->indexName}'";
+
+        $result = Db::fetchAll($sql);
+
+        if (empty($result)) {
+            // No index present - should be added
+            return true;
+        }
+
+        // Check that the $result contains all the required column names. This is required as there was a previous index
+        // with the same name that only consisted of two columns. We want to check this index is built with all three.
+        // $diff will be empty if all three columns are found, meaning that the index already exists.
+        $diff = array_diff(['idsite', 'idvisitor', 'visit_last_action_time'], array_column($result, 'Column_name'));
+
+        if (!$diff) {
+            return false;
+        }
+
+        return true;
+    }
+}
\ No newline at end of file