Migration_20260609_120000_Db.php — MailPoet – Newsletters, Email Marketing, and Automation 5.36.0

mailpoet / lib / Migrations / Db / Migration_20260609_120000_Db.php

mailpoet / lib / Migrations / Db Last commit date

Migration_20260609_120000_Db.php

41 lines

1	<?php declare(strict_types = 1);
2
3	namespace MailPoet\Migrations\Db;
4
5	if (!defined('ABSPATH')) exit;
6
7
8	use MailPoet\Entities\SubscriberEntity;
9	use MailPoet\Migrator\DbMigration;
10
11	class Migration_20260609_120000_Db extends DbMigration {
12	public function run(): void {
13	$subscribersTable = $this->getTableName(SubscriberEntity::class);
14
15	// Index (deleted_at, created_at) so the subscribers listing's default view
16	// (non-trashed, newest first) reads its rows straight from the index:
17	// `deleted_at IS NULL` pins the first column, so entries come out ordered
18	// by created_at — and by id within ties, since InnoDB appends the primary
19	// key to secondary indexes. Without it the query has no usable index at
20	// all (every other index leads with another column) and degrades to a
21	// full-table scan plus a filesort of every non-trashed subscriber.
22	//
23	// A single-column created_at index would serve the same query slightly
24	// worse (it scans trashed rows too), but the important difference shows
25	// under `deleted_at IS NULL` filtering: a single-column index is then
26	// fully bound, which qualifies it as a rowid-ordered scan that the
27	// optimizer can combine into an index-merge intersect with other
28	// status/deleted_at indexes — a plan that materializes and filesorts
29	// millions of rows on large sites. With created_at as an unbound second
30	// column, this index never qualifies, so that plan cannot be built from
31	// it. The deleted_at prefix also covers Trash counts
32	// (`deleted_at IS NOT NULL`) as an index-only range scan.
33	if (!$this->indexExists($subscribersTable, 'deleted_at_created')) {
34	$this->connection->executeQuery(
35	"ALTER TABLE `{$subscribersTable}`
36	ADD INDEX `deleted_at_created` (`deleted_at`, `created_at`)"
37	);
38	}
39	}
40	}
41