← All changes | database/Migrations/ConversationsMigrator.php +7 -14 trunk → 2.1.0 View file →

		@@ -28,10 +28,9 @@
28	28	`created_at` TIMESTAMP NULL,
29	29	`updated_at` TIMESTAMP NULL,
30	30	INDEX `idx_ticket_id` (`ticket_id`),
31	31	INDEX `idx_person_id` (`person_id`),
32		- INDEX `idx_created_at` (`created_at`),
33		- INDEX `idx_person_id_created_at` (`person_id`, `created_at`)
	32	+ INDEX `idx_created_at` (`created_at`)
34	33	) $charsetCollate;";
35	34	$created = dbDelta($sql);
36	35	return $created;
37	36	} else {
		@@ -63,26 +62,20 @@
63	62	$existing_index_names[] = $index->Key_name;
64	63	}
65	64
66	65	// Desired indexes — keys and values are all hardcoded string literals.
67		- // idx_person_id_created_at speeds up Advanced Reports queries that filter
68		- // fs_conversations by an agent's person_id AND a date range together —
69		- // without it MySQL falls back to idx_person_id alone and scans an agent's
70		- // entire conversation history before filtering by date, which gets very
71		- // slow on large installs (proven ~100x slower on a 300k-ticket dataset).
72	66	$indexes = [
73		- 'idx_ticket_id' => '`ticket_id`',
74		- 'idx_person_id' => '`person_id`',
75		- 'idx_created_at' => '`created_at`',
76		- 'idx_person_id_created_at' => '`person_id`, `created_at`',
	67	+ 'idx_ticket_id' => 'ticket_id',
	68	+ 'idx_person_id' => 'person_id',
	69	+ 'idx_created_at' => 'created_at',
77	70	];
78	71
79	72	// Add missing indexes. $table is esc_sql()'d above; $index_name and
80		- // $columns are hardcoded array literals — no user input reaches this query.
81		- foreach ($indexes as $index_name => $columns) {
	73	+ // $column_name are hardcoded array literals — no user input reaches this query.
	74	+ foreach ($indexes as $index_name => $column_name) {
82	75	if (!in_array($index_name, $existing_index_names)) {
83	76	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
84		- $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");
	77	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
85	78	}
86	79	}
87	80	}
88	81	}