Index Supply / SQL API

Examples

Examples

World App User Migration (case expressions)
Transfer(address indexed from, address indexed to, uint256 value)
SELECT block_num, COUNT (DISTINCT "to") AS migrated_users, SUM (CASE WHEN address = 0x2cfc85d8e48f8eab294be644d9e25c3030863003 THEN value ELSE 0 END ) AS wld, SUM (CASE WHEN address = 0x03c7054bcb39f7b2e5b2c7acb37583e32d70cfa3 THEN value ELSE 0 END ) AS btc, SUM(CASE WHEN address = 0x4200000000000000000000000000000000000006 THEN value ELSE 0 END ) AS eth, SUM (CASE WHEN address = 0x79a02482a880bce3f13e09da970dc34db4cd24d1 THEN value ELSE 0 END ) AS usdc FROM transfer WHERE "from" = 0xC6968c6DF1a2C31Ac66B42945BbaD91635a0095B GROUP BY block_num ORDER BY block_num DESC LIMIT 20
Daimo Name Registrations
Registered(bytes32 indexed name, address indexed addr)
select block_num, h2s(name) name, addr from registered where address = 0x4430A644B215a187a3daa5b114fA3f3d9DeBc17D order by block_num desc
ERC-1155 Transfer Batch (arrays)
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 = 21258465 and log_idx = 4
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
ERC721 Current Token Holders (self join)
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 = 0xE81b94b09B9dE001b75f2133A0Fb37346f7E8BA4 and t2.tokenId is null
Base Name Registrations
NameRegistered(string name, bytes32 indexed label, address indexed owner, uint256 expires)
select tx_hash, name, owner from nameregistered where address = 0x4ccb0bb02fcaba27e82a56646e81d8c5bc4119a5 order by block_num desc limit 100
Zora Mint Comments from a Random User
MintComment(address indexed sender, address indexed collection, uint256 indexed tokenId, uint256 quantity, string comment)
select comment from mintcomment where sender = 0x1d14d9e297dfbce003f5a8ebcf8cba7faee70b91

History

This query is taking too much time.

Reasons Why

  1. The Event is common and has millions of rows. For instance: Transfer or Swap
  2. The Event and Contract have millions of rows. For instance: USDC
  3. The Event and Contract are common and the query has a predicate on a non-indexed field.

    When an Event contains an indexed field (eg Foo(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 all Foo Events `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.
  4. There may be a bug! Please email: support@indexsupply.com

Tips for Fast Queries

  1. 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.
  2. Use `where block_num > X` or `where block_num >= X and block_num < Y` to paginate through a large dataset. In general, adding block_num predicates to your query will positively impact performance.

    In fact, live queries are sub-millisecond becase they only query recent blocks.
  3. 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.
  4. 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.