The published_at column has no database index.
Core indexes both created and changed on node_field_data, but the timestamp this module adds has none. A common use of the field is sorting content lists by publication date, and without an index every such view or query filesorts the whole table.
On our site (~14k nodes) a views query sorting by published_at takes ~20ms without the index and ~2ms with it.
Create a view of published nodes sorted by "Published on" and EXPLAIN the query: no key is used for the sort, MySQL falls back to a filesort over all rows.
Minimal example on a table with ~15k nodes:
EXPLAIN ANALYZE SELECT nid, title, published_at FROM node_field_data
WHERE status = 1 ORDER BY published_at DESC LIMIT 10;
Without the index, all rows are read and filesorted, 10.4ms:
-> Sort: published_at DESC, limit input to 10 row(s) per chunk (actual time=10.4 rows=10)
-> Index lookup using node__status_type (status=1) (actual time=0.06..9.6 rows=14028)
With the index, 12 rows are read in index order, 0.06ms:
-> Filter: (status = 1) (actual time=0.04..0.05 rows=10)
-> Index scan using node_field__published_at__value (reverse) (actual time=0.03..0.05 rows=12)
Declare the index in PublicationDateItem::schema() so new installs get it automatically, and add an update hook for existing installs.
Review.
None.
None.
New index on the published_at column (node_field_data and node_field_revision).
Show commandsStart within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
changes
Comments
Comment #3
herved commentedComment #4
herved commentedComment #6
mably commentedThanks for this, the missing index is a good catch and the before/after numbers make the case well. I reviewed the MR and pushed a small follow-up commit (819da61) for one blocker.
In the update hook,
publication_date_update_30001()was defined inside_publication_date_populate_database_field()rather than at the top level: it was indented one level, and the helper closing brace landed after it. Sincehook_update_Nis only discovered as a top-level function, the hook would never be registered, so existing sites would not get the index (only fresh installs would), and if that helper ever ran twice in one request PHP would fatal with "Cannot redeclare function". The follow-up moves the hook out to the top level; no logic change.The rest looks right: the
schema()addition indexes new installs on both node_field_data and node_field_revision, and the installation test confirms that path.One gap remains: the test only exercises the fresh-install path (schema()), not the update hook, which is why the pipeline stayed green despite the above. It would be worth adding coverage that runs the update on an already-installed site and asserts the index appears, ideally on both MySQL and Postgres since the module supports both and an index-only updateFieldStorageDefinition change is occasionally missed.
Comment #7
mably commentedI rebased this branch onto 3.x and retargeted the MR to 3.x (the default branch), so the index lands where new work goes.
That also clears the phpstan warning noted above: it was failing behind allow_failure only on the 3.0.x base, from a deprecated
NodeStorage::revisionIds()call in the installation test that 3.x already fixed in #3567175. After the rebase the diff is just the three index files, and phpstan is clean. The earlier follow-up movingpublication_date_update_30001()to the top level is preserved in the rebased history.Comment #9
mably commentedComment #11
herved commentedoh what a silly mistake I did there :) Thank you @mably, looks good now.