Examples
Transactions by Account Address
select
block_timestamp::date,
hash,
"to",
substring(input, 1, 4) as "4b"
from txs
where "from" = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
Nested Tuples: Morpho Blue Market Creation
CreateMarket(bytes32 indexed id, (address loanToken, address collateralToken, address oracle, address irm, uint256 lltv) marketParams)
select
id,
marketParams->>'loanToken' as loan_token,
marketParams->>'collateralToken' as collateral,
marketParams->>'lltv' as lltv
from createmarket
where address = 0xBBBBBbbBBb9cC5e90e3b3Af64bdAF62C37EEFFCb
order by block_num desc
limit 100
SUM/CASE: ERC20 Transfers Sent/Received per Token
Transfer(address indexed from, address indexed to, uint256 value)
select
address token,
sum(
case
when "from" = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
then 1
else 0
end
) sent,
sum(
case
when "to" = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
then 1
else 0
end
) received
from transfer
where (
"to" = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
or "from" = 0xd8da6bf26964af9d7eed9e03e53415d37aa96045
)
and block_num > 25300000
group by token
order by 2 desc, 3 desc
limit 25
ARRAYS: ERC-1155 Transfer Batch
TransferBatch(address indexed operator, address indexed from, address indexed to, uint256[] ids, uint256[] amounts)
select
log_idx,
"from",
"to",
ids,
amounts
from transferbatch
where block_num = 25444605
and log_idx = 607
COUNT/DISTINCT: Unique Senders/Receivers of ERC-721 Transfers per Block
Transfer(address indexed from, address indexed to, uint indexed tokenId)
select
block_num,
count(distinct(tx_hash)) transactions,
count(distinct("from")) senders,
count(distinct("to")) receivers
from transfer
group by block_num
order by block_num desc
limit 1
SELF JOIN: Recent Nouns Token Holders
Transfer(address indexed from, address indexed to, uint indexed tokenId)
select t1."to", t1.tokenId, t1.block_num
from transfer t1
left join transfer t2
on t1.tokenId = t2.tokenId
and t1.block_num < t2.block_num
and t1.address = t2.address
where t1.address = 0x9C8fF314C9Bc7F6e59A9d9225Fb22946427eDC03
and t1.block_num > 25300000
and t2.tokenId is null
order by t1.block_num desc
Fun: ENS Name Registrations
NameRegistered(string name, bytes32 indexed label, address indexed owner, uint256 baseCost, uint256 premium, uint256 expires)
select
tx_hash,
name,
owner
from nameregistered
where address = 0x253553366Da8546fC250F225fe3d25d0C782303b
order by block_num desc
limit 100
History
This query is taking too much time.
Reasons Why
- The Event or Function is common and has millions of rows. For instance:
TransferorSwap - The Event or Function and Contract have millions of rows. For instance:
USDC -
The Event/Function and Contract are common and the query has a predicate on a non-indexed field.
When an Event or Function contains an indexed field (egFoo(address indexed bar, uint256 baz)) and there is a query`select baz from foo where bar = 0x... and baz > 42`the query planner will use a database index to find allFooEvents`where bar = 0x...`but if that set contains millions of rows then the database will have to scan each of them checking for`baz > 42`–which is expensive. - There may be a bug! Please email: support@indexsupply.com
Tips for Fast Queries
- Use
`where address = 0x000...000`when possible. If a particular event is popular (eg Transfer(...)) and if you are only interested in Transfers for a particular contract, then adding the address predicate will speed up the query. -
Use
`where block_num > X`or`where block_num >= X and block_num < Y`to paginate through a large dataset. In general, addingblock_numpredicates to your query will positively impact performance.
In fact, live queries are sub-millisecond because they only query recent blocks. - Consider using events with indexed fields. Each indexed field in the event signature is indexed in the database. If you are designing a new contract, keep this in mind.
- Finally, if you need low latency access to non-indexed data, the Dedicated plan will allow you to create custom database indexes over non-indexed data resulting in sub-millisecond queries. Please create an account and reach out to support@indexsupply.com for assistance.