ElementsProject / ElementsProject/lightning
SQL plugin: translate short_channel_id strings to integers before querying SQLite
- 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.