How would I be able to improve the following joined query?

[brc55]

Learning about joins and sub searches. What's the following query executing and would there be a way to make it more efficient?

index=old_indexstats count values(d) as d by username | join type=inner username [search index=new_index | stats count by username ]

I believe it starts by searching and counting usernames in the new index however, am getting mixed up after that.

[richgalloway]

The first step in the query is to count all events and list the unique values of field 'd' for all usernames in old_index.  The second step is to count all events for each username in new_index.  Finally, the two sets of results are merged based on common username values.  If there is no match, the count from new_index is retained.

Here's one alternative query that should be more efficient.

index=old_index
| stats count as old_count values(d) as d by username 
| append [search index=new_index | stats count as new_count by username ]
| stats values(*) as * by username