So, am I correct in the following thinking? (Because none of the numbers match for this example user, live nor deleted: Edit Counter says 252 live & 13 deleted; and Pages says 250 live (+7 live redirects) & 11 deleted.)

1. Pages created and still alive:

Distinct page IDs from all revisions by this user where there's no parent revision,
but not including those that have a matching 'move' log entry
(where 'matching' is the same page ID and user ID).

This was:

SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
FROM revision_userindex
WHERE rev_user = 90672 AND rev_parent_id = 0

but should be:

SELECT 'created-live' AS `key`, COUNT(DISTINCT rev_page) AS `val`
FROM revision_userindex r
	LEFT JOIN logging_userindex l
		ON (
			r.rev_user = l.log_user
			AND r.rev_page = l.log_page
			AND (log_action = 'move' OR log_action = 'move_redir')
		)
WHERE
	rev_user = 90672
	AND rev_parent_id = 0
	AND l.log_id IS NULL

But I'm not sure that's correct, because the 'matching' log entry is for the moved page rather than the redirect. ("In the case of a page move, this is set to the page_id of the moved page (since Gerrit change 157872). Formerly, it was the page_id of the redirect, or 0 if the page was moved without creating a redirect.")

2. Pages created and since deleted (by someone else):

Distinct page IDs from all archived revisions by this user where there's no parent revision,
but not including those that have a matching 'move' log entry
(where 'matching' is the same page ID and user ID).

This is currently:

SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
                FROM archive_userindex
                WHERE ar_user = 90672 AND ar_parent_id = 0

but should be:

SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
FROM
	archive_userindex
	LEFT JOIN logging_userindex
		ON (
			ar_user = log_user
			AND ar_page_id = log_page
			AND (log_action = 'move' OR log_action = 'move_redir')
		)
WHERE
	ar_user = 90672
	AND ar_parent_id = 0

Sorry for my confusion; I'm sure there's some simple thing I'm missing here, or I'm just not familiar enough with the relationship between logging and archiving.

So I'm thinking that the correct number is to be got by this query:

SELECT 'created-deleted' AS `key`, COUNT(DISTINCT ar_page_id) AS `val`
FROM $archiveTable LEFT JOIN $loggingTable ON (
    ar_title = log_title
    AND ar_namespace = log_namespace
    AND ar_page_id = log_page
    AND (log_action = 'move' OR log_action = 'move_redir')
)
WHERE ar_user = :userId AND ar_parent_id = 0 AND log_id IS NULL

but it really is a bit slow. (It's quicker when joining on ar_page_id, but that's not always available it seems, for older data.)

@Samwilson: Isn't the count in "Edit counter" supposed to exclude all redirects anyway? Not sure I understand why redirects created during page move would be an issue in that case.

No, this is about counting how many pages a user has created that have since been deleted. So we were just looking at the number of unique page IDs referenced in archived revisions that have no parent revision. But that includes pages created as redirects, which we don't want.

We could join to the page table instead of the logging table (and filter on page_is_redirect), but that would change it to exclude pages that were redirects at the time the page was deleted, rather than at creation.

@MusikAnimal do you have some advice?

All things considered, I'm thinking we should just show the raw pages created count as we are now, and leave a footnote explaining that all pages are included. Or we can just remove this stat altogether. Perfecting it apparently is not going to be cheap, and after all, it's not the primary use case for this tool.

Good idea; keep it simple.

PR: https://github.com/x-tools/xtools/pull/64