← All changes | database/Migrations/ConversationsMigrator.php +3 -12 2.4.0 → 2.4.5 View file →

		@@ -49,14 +49,11 @@
49	49	public static function addMissingIndexes($table)
50	50	{
51	51	global $wpdb;
52	52
53		- // $table is always $wpdb->prefix . 'fs_conversations' — not user input.
54		- // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
55		- // cannot quote identifiers in WP < 6.2 (no %i placeholder available).
	53	+ // Our own table name, not user input. esc_sql() because %i needs WP 6.2+.
56	54	$table = esc_sql($table);
57	55
58		- // Get existing indexes
59	56	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
60	57	$existing_index_names = [];
61	58
62	59	foreach ($existing_indexes as $index) {
		@@ -62,14 +59,10 @@
62	59	foreach ($existing_indexes as $index) {
63	60	$existing_index_names[] = $index->Key_name;
64	61	}
65	62
66		- // 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).
	63	+ // idx_person_id_created_at serves Advanced Reports' agent + date range queries.
	64	+ // Without it MySQL scans the agent's whole history via idx_person_id.
72	65	$indexes = [
73	66	'idx_ticket_id' => '`ticket_id`',
74	67	'idx_person_id' => '`person_id`',
75	68	'idx_created_at' => '`created_at`',
		@@ -75,10 +68,8 @@
75	68	'idx_created_at' => '`created_at`',
76	69	'idx_person_id_created_at' => '`person_id`, `created_at`',
77	70	];
78	71
79		- // 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	72	foreach ($indexes as $index_name => $columns) {
82	73	if (!in_array($index_name, $existing_index_names)) {
83	74	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
84	75	$wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");