<?php
$_db_migration_to = '10.05'; // 3.0.10
if (!isset($migrations) || !is_object($migrations))
	die("This file cannot be executed directly");
$migrations->CheckValid($_db_migration_to);
//===========================================================================================



$migrations->Run('20240312_add_indexes_projects_display_order.php', <<<'DB_UPDATE_FILE'
<?php

/**
 * BT-4553
 * 
 * Added two indexes to solve a performance issue relating to the 'My Tasks' component.
 * 
 * The SQL for the 'My Tasks' component joins onto `projects_display_order` table, using a where clause
 * for `task_id` and `user_id`.
 * 
 * Previously there was no index for `task_id` and `user_id`, therefore a full table scan
 * was occuring. 
 * 
 * Adding the two indexes will stop a full table scan being done, thus leading better performance
 * and optimization.
 * 
 * Note: This migration has been applied twice, as the previous migration has been merged forward without
 * updating the internal version thus causing the migration to be skipped (v10 > v9)
 * 
 */

/** @var \Claromentis\Setup\SetupFacade $migrations */
$logger = $migrations->getLogger();

$logger->notice('Adding composite index `task_id` and `user_id` to `projects_display_order` table');

$database = $migrations->GetDb();

$database->CreateIndex('projects_display_order', 'task_id_user_id', 'task_id', 'user_id');

$logger->notice('Succesfully added composite index `task_id` and `user_id` to `projects_display_order` table');
DB_UPDATE_FILE
);


//===========================================================================================
$migrations->SetVersion('10.05');
