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

# We will select our fields from an inner join
SELECT
  revision.rev_page AS "Created Page ID",
  revision.rev_timestamp AS "Creation Timestamp",
  page.page_title AS "Created Article",
  page.page_is_redirect AS "Redirected Later On?"

FROM (
  # Stitch in the article name
  revision INNER JOIN page ON revision.rev_page = page.page_id
)

WHERE (
  # Imported pages may throw off some parent IDs ...
  # ... but it should mostly be valid for page creation
  revision.rev_parent_id = 0 AND

  # Also filter down to articles in Main namespace
  page.page_namespace = 0
  # NOTE: Leave in redirects to avoid missing moved pages
)

# I know ORDER BYs are discouraged, but that's the core logic
ORDER BY revision.rev_timestamp ASC
# TESTING: Start with a small limit to gauge time
LIMIT 100;

SQL

Checking query status...