Queries, Indexes and Multisite Installs

An index is a promise you make to the query planner, and the planner is not obliged to accept it. The plugin's logs table carries seven indexes, which looks generous until you run the queries against a real dataset and count how many the planner chooses. On the lab install it chose four, and one of the seven never appeared in a plan at all.

Everything here comes from the plugin's own SQL against WordPress 7.0.7 and MySQL 9.6.0, with five thousand rows in wp_sfct_delivery_logs, skewed so that tracking_lookup is 56 percent of the table, which is what a real store looks like.

The seven keys, read from SHOW INDEX

The table is created by dbDelta() from the statement in lesson 04, and the indexes it produces are exactly the ones the statement names:

KEY order_id (order_id)
KEY tracking_id (tracking_id)
KEY company_id (company_id)
KEY status (status)
KEY log_type (log_type)
KEY created_at (created_at)
KEY log_type_created (log_type, created_at)

Reading the plugin's source gives you the same list, but the database is the authority on two things the source cannot tell you: the order of the columns inside a composite key, and whether the key exists at all on a site that was installed by an older version. On a real install, SHOW INDEX FROM wp_sfct_delivery_logs returns nine rows, because the composite key contributes two: log_type_created at Seq_in_index 1 for log_type and 2 for created_at.

Of the queries the plugin runs against this table, four have a filter worth planning for:

-- includes/tracking.php: the public tracking timeline
SELECT * FROM wp_sfct_delivery_logs
WHERE tracking_id = %s AND log_type <> %s ORDER BY created_at ASC;

-- includes/tracking.php: has this order already been given a tracking id?
SELECT id FROM wp_sfct_delivery_logs
WHERE order_id = %d AND tracking_id = %s AND log_type = %s LIMIT 1;

-- includes/admin-pages.php: the newest dashboard entries
SELECT * FROM wp_sfct_delivery_logs
WHERE log_type <> %s ORDER BY created_at DESC, id DESC LIMIT %d;

-- includes/tracking.php: trim the oldest rows once the table passes a hundred
DELETE FROM wp_sfct_delivery_logs ORDER BY created_at ASC LIMIT %d;

Notice what the third query does with log_type. <> is a negative comparison, and no ordinary B-tree index can seek on "everything except this value"; the only useful part of that query is the ordering, so the planner reads backwards along created_at and discards rows until it has ten. That is fine when ten of the newest twenty rows qualify. It is not fine when tracking_lookup dominates the recent rows, which in this dataset it does.

What the planner chose, measured

Running EXPLAIN FORMAT=TRADITIONAL against each shape on the five-thousand row table gives the answer in one column:

Query shape	key	key_len	rows	Extra
tracking_id + log_type <>, ordered	tracking_id	763	9	Using where; Using filesort
order_id + tracking_id + log_type	tracking_id, order_id	763,9	1	Using intersect(...)
log_type <>, ordered by created_at DESC	created_at	5	22	Backward index scan
log_type = alone	log_type_created	323	491	Using index
log_type = + created_at >=	log_type_created	328	300	Using index condition
created_at between two dates	created_at	5	631	Using where; Using index

The key_len numbers are the useful part. 5 for a datetime is one key part. 323 is a varchar(80) in utf8mb4 with a length byte and nullability. 328 is that 323 plus the 5 for created_at, which is how you see the planner using both parts of the composite key. 763 is varchar(190) in utf8mb4, the tracking id, indexed in full rather than truncated to a prefix.

One plan is worth a second look. The per-order duplicate check used index_merge with Using intersect(tracking_id, order_id), which means MySQL scanned two indexes and intersected the row ids rather than picking one: the planner had no single key that covered the shape. A composite on (order_id, tracking_id) would have served it with one seek, and the plugin does not have one. On five thousand rows that costs nothing measurable. On five million it is the difference between a lookup and a merge.

The leftmost-prefix rule, with the composite as the example

A composite index on (log_type, created_at) is sorted by log_type first and by created_at within each log_type. That physical order is why the index serves a filter on log_type and a filter on log_type plus created_at, and why it cannot serve created_at alone. To find everything created after Tuesday, the engine would read every log_type block in full, which means reading the whole index, at which point the smaller single-column key wins.

Measured confirmations, all on the same five thousand rows:

• WHERE log_type = 'api_call' used log_type_created with Using index, so the query was answered from the index alone.
• Adding AND created_at >= ... used the same key and narrowed it from 323 to 328 bytes of key length, turning the second column into a range.
• WHERE created_at BETWEEN ... used created_at, exactly as the rule predicts.
• Adding ORDER BY created_at DESC to a log_type filter lets the composite satisfy the ordering, because created_at is the second part and MySQL can scan it backwards. ORDER BY status would not.

The rule to carry away is order of columns, not number of columns. Put the column that appears in an equality filter first and the column you range or sort on second, and expect that anything skipping the first column gets no help at all.

Is the single-column log_type key dead weight?

A single-column key on the first column of a composite key is the classic candidate for removal, and the usual argument is that the composite covers everything the single-column key covers. That argument is about the schema. The measurement says something simpler: the planner never chose it.

In every plan above, the equality filter on log_type used log_type_created, not log_type. Dropping the single-column key and re-running the identical EXPLAIN statements produced identical plans: same keys, same key lengths, same row estimates. MySQL's own sys schema calls it out without being asked:

SELECT redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_name = 'wp_sfct_delivery_logs';

+----------------------+---------------------+
| redundant_index_name | dominant_index_name |
+----------------------+---------------------+
| log_type             | log_type_created    |
+----------------------+---------------------+

And it is not free. With the key, the table reports 1,424 KiB of index; without it, 1,264 KiB. That is 160 KiB for five thousand rows, 32 bytes per row, plus a write on every insert into a table the plugin writes to on every status change and tracking lookup.

So drop it? Not so fast, and this is where measuring beats reasoning. The measurement above is five thousand rows with a 56/44 split between tracking_lookup and everything else. The optimizer's choice depends on cardinality and on how well the leading column discriminates, so a different dataset can make the same key attractive. Before removing an index on a production table, run EXPLAIN with the index present, drop it, run the queries again, and compare both the plans and the wall time. Here, three hundred executions of the equality query came in at 63.8 ms with the key and 61.7 ms without, which is noise on a laptop. Removing it would save disk and write cost without changing reads: a real optimisation on a table that grows, and a different claim from "this index is redundant".

Multisite: the prefix is not what you think it is

On a default single-site install, $wpdb->prefix and $wpdb->base_prefix both return wp_, which is why the distinction gets ignored until it matters. On a network, base_prefix stays wp_ for the whole network and prefix becomes wp_2_, wp_3_ and so on for each site, tracking whichever site is current.

The table helpers use $wpdb->prefix, so they return wp_2_sfct_delivery_logs on the second site of a network, and the same call inside a switch_to_blog() block returns the switched site's table. That is why they are functions. A constant would hold whichever value was correct when the plugin file loaded, which is the main site, so every subsite would read and write the main site's logs. Measured on a two-site network: wp_sfct_delivery_logs and wp_2_sfct_delivery_logs both present, with blog 2's settings stored in wp_2_options.

Network activation has to handle the sites that already exist, and that is the first half of the install path:

function sfct_activate( $network_wide = false ) {
	if ( is_multisite() && $network_wide && function_exists( 'get_sites' ) ) {
		$site_ids = get_sites( array( 'fields' => 'ids', 'number' => 0 ) );

		foreach ( $site_ids as $site_id ) {
			switch_to_blog( (int) $site_id );
			sfct_install_current_site();
			restore_current_blog();
		}

		return;
	}

	sfct_install_current_site();
}

number => 0 is how get_sites() is told to return every site rather than the default twenty. Without the loop, a network activation would create tables on whichever site the admin happened to be on and silently leave the rest without them, which on a store network means the first order on subsite four throws a database error.

The second half is the part with a trap in it:

function sfct_install_tables_for_new_site( $site ) {
	if ( ! function_exists( 'is_plugin_active_for_network' ) ) {
		require_once ABSPATH . 'wp-admin/includes/plugin.php';
	}

	if ( ! is_plugin_active_for_network( SFCT_BASENAME ) ) {
		return;
	}

	switch_to_blog( (int) $site->blog_id );
	sfct_install_current_site();
	restore_current_blog();
}

The trap is the switch_to_blog(), and whether it is needed depends on something you cannot see by reading the plugin. WordPress fires wp_initialize_site from inside wp_insert_site(), and I instrumented that call on the lab network to see what state it fires in:

wp_insert_site        site=2  current_blog=1  $wpdb->prefix=wp_
wp_initialize_site#5  site=2  current_blog=1  $wpdb->prefix=wp_

The new site's rows are in wp_blogs. Its tables do not exist yet inside the callback, and the current blog is still the caller's. If the plugin had run sfct_install_current_site() without switching, $wpdb->prefix would still have been wp_, and the install would have created nothing new, because the main site's tables were already there. Then blog 2 would run with a schema pointing at the main site's tables, and every tracking record it wrote would land in blog 1's data. That is the failure the plugin avoids, and the same trap catches plugins that assume wp_initialize_site arrives with the new site already current.

The guard matters for the same reason. is_plugin_active_for_network() reads active_sitewide_plugins from the network options, not the current blog, so it answers correctly before the switch. Without it, a plugin that one site activated would create tables on every new site in the network. Measured: on a site created while the plugin was network-active, both tables appeared under wp_3_; on the next site, created after the network activation was removed, no tables appeared at all.

Two bugs worth naming

The plugin registers both the modern hook and its deprecated predecessor:

add_action( 'wp_initialize_site', 'sfct_install_tables_for_new_site', 10, 1 );
add_action( 'wpmu_new_blog', 'sfct_install_tables_for_new_blog_legacy', 10, 1 );

wpmu_new_blog was deprecated in WordPress 5.1, and the replacement is wp_initialize_site. Keeping both is harmless here because sfct_install_current_site() is idempotent and the deprecated hook only fires when something listens to it, which is this plugin. It is still dead code on a plugin whose stated minimum is 6.2, and the legacy function duplicates thirteen lines.

The other is in the export path. includes/ajax.php line 242 builds its query with string concatenation and esc_sql() around the table name, rather than the %i placeholder every other query in the plugin uses. It is not exploitable, because the table name comes from $wpdb->prefix and not from a request, but it is the one query that does not look like the others, and inconsistency in prepared statements is how the first real injection gets in.

Show the solution and the reasoning

On this dataset the answers were: tracking_id for the timeline query, index_merge over tracking_id and order_id for the duplicate check, created_at with a backward index scan for the dashboard, and log_type_created for both equality shapes. Dropping the single-column key changed none of them. The equality query measured 63.8 ms with the key and 61.7 ms without, and the index shrank by 160 KiB.

If your numbers disagree, that is the point of running it. The optimizer's decision depends on how many distinct values log_type has, how evenly the rows spread across them, and how much of the table the filter matches. A table where one value is 99 percent of the rows makes the single-column key even less useful, because the index scan and the table scan cost about the same. A table with fifty evenly distributed types can make it look valuable, and the composite still covers that filter, because log_type is its first column.

The reasoning that matters is about the shape of the evidence. Reading the schema tells you an index exists. Reading the queries tells you an index could be used. Only EXPLAIN tells you whether it was, and only a timing run tells you whether the difference matters. It is also why "redundant" is the wrong word for the single-column key even though MySQL's own sys view uses it: that view compares index definitions, not workload, so it cannot know that your queries order by created_at often enough to make the composite the better choice every time.

Dropping an index is an ALTER TABLE that rebuilds, so on a large table it belongs in a maintenance window, and if you are wrong the queries that relied on it go from a seek to a scan. The cheap version of this work is to stop adding redundant keys in the first place: write the composite you need, and do not also write a single-column key for its first column.