Vitality Metrics: Oldest Pages (EN)

This query simply finds the oldest continuously existing pages on Wikipedia (EN). This will be used as one metric for estimating article vitality.

The underlying logic is the Lindy effect (see https://en.wikipedia.org/wiki/Lindy_effect).

Because of how the database indices are set up, we don't attempt to join with the page table at all.

SQL

# Set a query timeout for politeness
SET STATEMENT max_statement_time = 300 FOR

# Indices don't allow for an efficient join ...
# ... so we'll just grab page IDs for now
SELECT
  revision.rev_page AS "Created Page ID",
  revision.rev_timestamp AS "Creation Timestamp"
FROM revision

WHERE (
  # Imported pages may throw off some parent IDs ...
  # ... but it should mostly be valid for page creation
  revision.rev_parent_id = 0 AND
  
  # OPTIMIZATION: Since this is stable, historical data ...
  # ... limit in advance (pre-2006 should allow ~500k)
  revision.rev_timestamp < 20060000000000
)

# We can't select article pages without an efficient join ...
# ... so we'll just oversample for now

# Ordering should be OK here since timestamp is indexed
ORDER BY revision.rev_timestamp ASC
# TESTING: Start with a small limit to gauge time
LIMIT 200000;

SQL

Checking query status...