← All changes | database/Migrations/NotificationUserMigrator.php +24 -2 2.7.0 → 2.10.0 View file →

		@@ -28,10 +28,11 @@
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`),
33		- INDEX `{$indexPrefix}_mto_id_oion` (`object_id`, `is_read`, `object_type`, `notification_type`)
	32	+ INDEX `{$indexPrefix}_uiou` (`user_id`, `is_read`, `object_type`, `updated_at`),
	33	+ INDEX `{$indexPrefix}_mto_id_oion` (`object_id`, `is_read`, `object_type`, `notification_type`),
	34	+ INDEX `{$indexPrefix}_created_uid` (`created_at`, `user_id`)
34	35	) $charsetCollate;";
35	36	dbDelta($sql);
36	37	} else {
37	38	self::maybeAlterDBColumns();
		@@ -78,7 +79,28 @@
78	79
79	80	if(!in_array($index2, $allIndexes)) {
80	81	// add this index
81	82	$wpdb->query("ALTER TABLE $table ADD INDEX `{$index2}` (`object_id`, `is_read`, `object_type`, `notification_type`)");
	83	+ }
	84	+
	85	+ $createdIndex = $wpdb->prefix . 'fcom_nu__created_uid';
	86	+
	87	+ if(!in_array($createdIndex, $allIndexes)) {
	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`)");
82	104	}
83	105	}
84	106	}