Confused about this indexer database design decision. Why are inner txs stored twice?. Take for example round 23645256 intra 62 in the txn table. They are stored initially in the itx field in round 23645256 intra 62 and then those same fields are repeated on intra 63, intra 64, and intra 65.
See image:
Why even bother repeating these rows? Seems like a waste of extra rows when this info is already available in the root txn.
This is optimized for querying, by structuring it this way we’re able to match on an inner transaction and return the root transaction.
For more efficient storage, it would probably be better to reconstruct the inner transaction tree from individual transactions. The downside is that would require a more complicated query and more post processing to reconstruct the original root transaction.
Some of these decisions were speculative. We didn’t know how inner transactions would evolve so we wanted to keep things simple and flexible. As you’ve observed, the cost to that simplicity is that inner transactions are stored twice.
The TxnCounter was not part of the consensus protocol when we launched, which is why you don’t see it until round 3317192 on mainnet. In addition, there were some empty blocks before it. The value would have been omitted until the first transaction after the protocol upgrade.
It looks like there was one transaction on round 3317192, which is why you see the value is 1. There were no transactions on round 3317193, so it remained 1. There was one transaction on round 3317194, so it was incremented to 2. Etc.
TxnCounter was added along with ASAs as a way to generate the unique IDs 
