← All changes | database/Migrations/NotificationUserMigrator.php +16 -1 2.7.7 → 2.10.0 View file →

		@@ -28,9 +28,9 @@
28	28	`user_id` BIGINT UNSIGNED NULL,
29	29	`is_read` TINYINT(1) UNSIGNED NULL DEFAULT 0,
30	30	`created_at` TIMESTAMP NULL,
31	31	`updated_at` TIMESTAMP NULL,
32		- INDEX `{$indexPrefix}_mto_id_uio` (`user_id`, `is_read`, `object_type`),
	32	+ INDEX `{$indexPrefix}_uiou` (`user_id`, `is_read`, `object_type`, `updated_at`),
33	33	INDEX `{$indexPrefix}_mto_id_oion` (`object_id`, `is_read`, `object_type`, `notification_type`),
34	34	INDEX `{$indexPrefix}_created_uid` (`created_at`, `user_id`)
35	35	) $charsetCollate;";
36	36	dbDelta($sql);
		@@ -85,7 +85,22 @@
85	85	$createdIndex = $wpdb->prefix . 'fcom_nu__created_uid';
86	86
87	87	if(!in_array($createdIndex, $allIndexes)) {
88	88	$wpdb->query("ALTER TABLE $table ADD INDEX `{$createdIndex}` (`created_at`, `user_id`)");
	89	+ }
	90	+
	91	+ /*
	92	+ * Every ticker poll counts a member's unread rows, and the live-notification
	93	+ * toast reads the newest of them. Without a user_id-leading index MySQL falls
	94	+ * back to the single-column is_read index - on a table where nearly every row
	95	+ * is unread that means scanning half the table, per poll, per member.
	96	+ *
	97	+ * user_id + is_read + object_type answer both queries as a covering index, and
	98	+ * the trailing updated_at removes the toast query's filesort as well.
	99	+ */
	100	+ $unreadIndex = $wpdb->prefix . 'fcom_nu__uiou';
	101	+
	102	+ if(!in_array($unreadIndex, $allIndexes)) {
	103	+ $wpdb->query("ALTER TABLE $table ADD INDEX `{$unreadIndex}` (`user_id`, `is_read`, `object_type`, `updated_at`)");
89	104	}
90	105	}
91	106	}