Best Practices

Follow these guidelines to write efficient queries and get the most out of Bilinear Labs.

Always Use Filters

Avoid full table scans. Always include a WHERE clause that narrows the block range or contract:

-- Good: filtered by block range
SELECT * FROM ethereum.events
WHERE block_number BETWEEN 19800000 AND 19900000
  AND event_name = 'Transfer';

-- Bad: scans everything
SELECT * FROM ethereum.events
WHERE event_name = 'Transfer';

Use LIMIT

Always include a LIMIT, especially during exploration:

SELECT * FROM ethereum.events
WHERE contract_address = '0xdAC17F958D2ee523a2206206994597C13D831ec7'
LIMIT 100;

Prefer Specific Columns

Select only the columns you need instead of SELECT *:

SELECT block_number, tx_hash, args->>'value' AS amount
FROM ethereum.events
WHERE event_name = 'Transfer'
LIMIT 100;

Use Time-Based Ranges

When you need recent data, filter by block_timestamp instead of scanning from genesis:

SELECT COUNT(*)
FROM ethereum.events
WHERE event_name = 'Swap'
  AND block_timestamp >= NOW() - INTERVAL '1 day';

Cache API Responses

For repeated queries, cache results on your side. The same block range will always return the same data since blockchain data is immutable.

Batch Queries

If you need data for multiple contracts, use IN instead of separate requests:

SELECT contract_address, COUNT(*) AS events
FROM ethereum.events
WHERE contract_address IN (
  '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48',
  '0xdAC17F958D2ee523a2206206994597C13D831ec7'
)
AND block_number > 19800000
GROUP BY contract_address;