I have clickhouse 24.11 and this is table structure that AI helped me to do:

CREATE TABLE ethereum.transfers
(
    `blockNumber` UInt64,
    `transactionIndex` UInt32,
    `transactionHash` LowCardinality(FixedString(66)) CODEC(ZSTD(3)),
    `timestamp` UInt32,
    `address` LowCardinality(Nullable(FixedString(42))),
    `from_` LowCardinality(Nullable(FixedString(42))),
    `to_` LowCardinality(Nullable(FixedString(42))),
    `logIndex` Nullable(UInt32),
    INDEX transfers_multi_address (address, from_, to_) TYPE set(100) GRANULARITY 4,
    INDEX transfers_from_block_skip (from_, blockNumber) TYPE bloom_filter GRANULARITY 4,
    INDEX transfers_to_block_skip (to_, blockNumber) TYPE bloom_filter GRANULARITY 4,
    PROJECTION transfers_address_search
    (
        SELECT *
        ORDER BY
            from_,
            to_,
            blockNumber,
            transactionIndex
    )
)
ENGINE = MergeTree
PRIMARY KEY blockNumber
ORDER BY (blockNumber, transactionIndex)
SETTINGS index_granularity = 8192, min_rows_for_wide_part = 1000000, min_rows_for_compact_part = 10000, min_bytes_for_compact_part = 10000000

I need query:

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE (from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205' or to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205') and blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500

or

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE from_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134

UNION ALL

SELECT
    transactionHash,
    from_ as from,
    to_ as to
FROM transfers
WHERE to_='0xda8cf15bc458a7b13735e7d04495f8cda53f3205'
AND blockNumber<=21405134
ORDER BY blockNumber DESC, transactionIndex ASC
LIMIT 500;

The problem that it loads a lot of time:
1296 rows in set. Elapsed: 0.290 sec. Processed 63.85 million rows, 139.89 MB (220.12 million rows/s., 482.29 MB/s.)

SELECT count(*)
FROM transfers

Query id: 46c6e414-ec2f-457b-9028-09e76dba1426

   ┌───count()─┐
1. │ 108681712 │ -- 108.68 million

here is explain:

    ┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Union                                                                                                                                                              │
 2. │   Filter                                                                                                                                                           │
 3. │     ReadFromMergeTree (transfers_address_search)                                                                                                                   │
 4. │     Indexes:                                                                                                                                                       │
 5. │       PrimaryKey                                                                                                                                                   │
 6. │         Keys:                                                                                                                                                      │
 7. │           from_                                                                                                                                                    │
 8. │           blockNumber                                                                                                                                              │
 9. │         Condition: and((blockNumber in (-Inf, 21405134]), (from_ in ['0xda8cf15bc458a7b13735e7d04495f8cda53f3205', '0xda8cf15bc458a7b13735e7d04495f8cda53f3205'])) │
10. │         Parts: 12/12                                                                                                                                               │
11. │         Granules: 12/13258                                                                                                                                         │
12. │   Expression (Project names)                                                                                                                                       │
13. │     Limit (preliminary LIMIT (without OFFSET))                                                                                                                     │
14. │       Sorting (Sorting for ORDER BY)                                                                                                                               │
15. │         Expression ((Before ORDER BY + Projection))                                                                                                                │
16. │           Expression                                                                                                                                               │
17. │             ReadFromMergeTree (ethereum.transfers)                                                                                                                 │
18. │             Indexes:                                                                                                                                               │
19. │               PrimaryKey                                                                                                                                           │
20. │                 Keys:                                                                                                                                              │
21. │                   blockNumber                                                                                                                                      │
22. │                 Condition: (blockNumber in (-Inf, 21405134])                                                                                                       │
23. │                 Parts: 15/15                                                                                                                                       │
24. │                 Granules: 13272/13272                                                                                                                              │
25. │               Skip                                                                                                                                                 │
26. │                 Name: transfers_multi_address                                                                                                                      │
27. │                 Description: set GRANULARITY 4                                                                                                                     │
28. │                 Parts: 15/15                                                                                                                                       │
29. │                 Granules: 13272/13272                                                                                                                              │
30. │               Skip                                                                                                                                                 │
31. │                 Name: transfers_to_block_skip                                                                                                                      │
32. │                 Description: bloom_filter GRANULARITY 4                                                                                                            │
33. │                 Parts: 9/15                                                                                                                                        │
34. │                 Granules: 7782/13272                                                                                                                               │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Any ideas how to optimize?