From 6dfe3fa8a51c01f74f6cfcc41976dc1633a9c9ce Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Mon, 20 Mar 2023 17:57:02 -0700
Subject: [PATCH] civicrm_cache - Replace wonky idx (group,path,date) with
 sensible idx (group,path)

Note: On the PR, I'll give a more thorough discussion for why the old index seems nonsensical.
---
 CRM/Core/DAO/Cache.php                       | 9 ++++-----
 CRM/Upgrade/Incremental/php/FiveSixtyOne.php | 2 ++
 xml/schema/Core/Cache.xml                    | 8 ++++++++
 3 files changed, 14 insertions(+), 5 deletions(-)

diff --git a/CRM/Core/DAO/Cache.php b/CRM/Core/DAO/Cache.php
index 09ee3bd4da..71f4552d31 100644
--- a/CRM/Core/DAO/Cache.php
+++ b/CRM/Core/DAO/Cache.php
@@ -6,7 +6,7 @@
  *
  * Generated from xml/schema/CRM/Core/Cache.xml
  * DO NOT EDIT.  Generated by CRM_Core_CodeGen
- * (GenCodeChecksum:8b1e9626632c80aadce6cae1fda86687)
+ * (GenCodeChecksum:7ef203eece225df98205318c7162ebd1)
  */
 
 /**
@@ -311,16 +311,15 @@ class CRM_Core_DAO_Cache extends CRM_Core_DAO {
    */
   public static function indices($localize = TRUE) {
     $indices = [
-      'UI_group_path_date' => [
-        'name' => 'UI_group_path_date',
+      'UI_group_name_path' => [
+        'name' => 'UI_group_name_path',
         'field' => [
           0 => 'group_name',
           1 => 'path',
-          2 => 'created_date',
         ],
         'localizable' => FALSE,
         'unique' => TRUE,
-        'sig' => 'civicrm_cache::1::group_name::path::created_date',
+        'sig' => 'civicrm_cache::1::group_name::path',
       ],
     ];
     return ($localize && !empty($indices)) ? CRM_Core_DAO_AllCoreTables::multilingualize(__CLASS__, $indices) : $indices;
diff --git a/CRM/Upgrade/Incremental/php/FiveSixtyOne.php b/CRM/Upgrade/Incremental/php/FiveSixtyOne.php
index cd79cbd7bc..69da6003eb 100644
--- a/CRM/Upgrade/Incremental/php/FiveSixtyOne.php
+++ b/CRM/Upgrade/Incremental/php/FiveSixtyOne.php
@@ -46,6 +46,8 @@ class CRM_Upgrade_Incremental_php_FiveSixtyOne extends CRM_Upgrade_Incremental_B
    */
   public function upgrade_5_61_alpha1($rev): void {
     $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);
+    $this->addTask(ts('Drop index %1', [1 => 'civicrm_cache.UI_group_path_date']), 'dropIndex', 'civicrm_cache', 'UI_group_path_date');
+    $this->addTask(ts('Create index %1', [1 => 'civicrm_cache.UI_group_name_path']), 'addIndex', 'civicrm_cache', [['group_name', 'path']], 'UI');
   }
 
 }
diff --git a/xml/schema/Core/Cache.xml b/xml/schema/Core/Cache.xml
index 6afd66a7b8..9e69ada0f1 100644
--- a/xml/schema/Core/Cache.xml
+++ b/xml/schema/Core/Cache.xml
@@ -38,6 +38,13 @@
     <comment>Unique path name for cache element</comment>
     <add>2.1</add>
   </field>
+  <index>
+    <name>UI_group_name_path</name>
+    <fieldName>group_name</fieldName>
+    <fieldName>path</fieldName>
+    <unique>true</unique>
+    <add>5.61</add>
+  </index>
   <index>
     <name>UI_group_path_date</name>
     <fieldName>group_name</fieldName>
@@ -45,6 +52,7 @@
     <fieldName>created_date</fieldName>
     <unique>true</unique>
     <add>4.2</add>
+    <drop>5.61</drop>
   </index>
   <field>
     <name>data</name>
-- 
2.25.1