ElementsProject / ElementsProject/lightning

SQL plugin: translate short_channel_id strings to integers before querying SQLite

Open
#8,941 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.1k
Forks
1k
Avg merge
4d 10h
Merged PRs (30d)
13

Description

When querying the forwards table by channel using the SQL plugin, queries like:
`SELECT * FROM forwards WHERE in_channel='735095x480x1'`
appear to perform a full table scan even when an index exists on the underlying in_channel_scid integer column. I think this is because the plugin converts the scid string to an integer at the application layer rather than translating it into the in_channel_scid type (integer) before passing the query to SQLite.
As a result, SQLite cannot use the index I created. Instead, I see:
```
"EXPLAIN QUERY PLAN SELECT * FROM forwards WHERE in_channel_scid=735095480001 OR out_channel_scid=735095480001 LIMIT 10"
QUERY PLAN
--SCAN forwards
```
**Expected behavior:**
The plugin should translate in_channel='735095x480x1' into the in_channel_scid before executing the query, allowing SQLite to use existing or user-created indices.
**Impact:**
The forwards table can grow to hundreds of thousands of rows on active routing nodes. Without index support, channel-specific queries become slow as the table grows.
**Workaround:**
Users can query the underlying SQLite database directly using integer-encoded scids, bypassing the plugin entirely.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.