IntersectMBO / IntersectMBO/cardano-db-sync
`consumed_by_tx_id` on `ma_tx_out`
- Dominant language
- Haskell
- Stars
- 318
- Forks
- 168
- PR merge metrics
- No merged PRs in 30d
Description
As far as I'm aware, it's currently not possible to get all unspent multi asset transaction outputs for a specific multi asset without doing one of:
- Scanning all rows of `ma_tx_out` for a given multi asset joined with `tx_out` filtered on `consumed_by_tx_id`
- Scanning all `consumed_by_tx_id IS NULL` on `tx_out` joined with `ma_tx_out` filtered for a given multi asset
- Creating a materialized view
The former ends up being far more performant, but still leads to slow queries for multi assets with many transaction outputs. Here's an example for the `/assets/:asset/addresses` query from Blockfrost:
```sql
SELECT
txo.address AS "address",
SUM(quantity) AS "quantity"
FROM ma_tx_out mto
JOIN multi_asset ma ON (mto.ident = ma.id)
JOIN tx_out txo ON (txo.id = mto.tx_out_id)
JOIN tx ON (tx.id = txo.tx_id)
WHERE txo.consumed_by_tx_id IS NULL
AND (encode(ma.policy, 'hex') || encode(ma.name, 'hex')) = $1
-- don't count utxos that are part of transaction that failed script validation at stage 2
AND tx.valid_contract = 'true'
GROUP BY txo.address
ORDER BY MIN(tx.id) ASC
LIMIT 100;
```
Would it make sense to include a `consumed_by_tx_id` field on `ma_tx_out` alongside the existing field on `tx_out` for this case?
Contributor guide
Assessment
This issue has not been assessed yet.