← All changes | database/Migrations/ConversationsMigrator.php +11 -13 2.3.0 → 2.4.5 View file →

		@@ -28,9 +28,10 @@
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`)
	32	+ INDEX `idx_created_at` (`created_at`),
	33	+ INDEX `idx_person_id_created_at` (`person_id`, `created_at`)
33	34	) $charsetCollate;";
34	35	$created = dbDelta($sql);
35	36	return $created;
36	37	} else {
		@@ -48,14 +49,11 @@
48	49	public static function addMissingIndexes($table)
49	50	{
50	51	global $wpdb;
51	52
52		- // $table is always $wpdb->prefix . 'fs_conversations' — not user input.
53		- // esc_sql() is the correct escaping for SQL identifiers; $wpdb->prepare()
54		- // 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+.
55	54	$table = esc_sql($table);
56	55
57		- // Get existing indexes
58	56	$existing_indexes = $wpdb->get_results("SHOW INDEX FROM `{$table}`"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
59	57	$existing_index_names = [];
60	58
61	59	foreach ($existing_indexes as $index) {
		@@ -61,21 +59,21 @@
61	59	foreach ($existing_indexes as $index) {
62	60	$existing_index_names[] = $index->Key_name;
63	61	}
64	62
65		- // Desired indexes — keys and values are all hardcoded string literals.
	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.
66	65	$indexes = [
67		- 'idx_ticket_id' => 'ticket_id',
68		- 'idx_person_id' => 'person_id',
69		- 'idx_created_at' => 'created_at',
	66	+ 'idx_ticket_id' => '`ticket_id`',
	67	+ 'idx_person_id' => '`person_id`',
	68	+ 'idx_created_at' => '`created_at`',
	69	+ 'idx_person_id_created_at' => '`person_id`, `created_at`',
70	70	];
71	71
72		- // Add missing indexes. $table is esc_sql()'d above; $index_name and
73		- // $column_name are hardcoded array literals — no user input reaches this query.
74		- foreach ($indexes as $index_name => $column_name) {
	72	+ foreach ($indexes as $index_name => $columns) {
75	73	if (!in_array($index_name, $existing_index_names)) {
76	74	// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared -- all identifiers are either esc_sql()'d or hardcoded literals.
77		- $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` (`{$column_name}`)");
	75	+ $wpdb->query("ALTER TABLE `{$table}` ADD INDEX `{$index_name}` ({$columns})");
78	76	}
79	77	}
80	78	}
81	79	}