> Any reason not to use a three-way join?
Thanks for this suggestion! It does solve the problem in SQLite v3.35, but makes it ~equally as fast as the original version in SQLite v3.31, which suggests the query planner is, impressively, supposed to run my original (more concise and readable?) version in effectively the same way.
Original plus Richard's workaround:
> ```
EXPLAIN QUERY PLAN SELECT count(_rowid_) FROM edge
WHERE +node_from IN temp.sub_nodes AND node_to IN temp.sub_nodes```
```
SEARCH TABLE edge USING COVERING INDEX edge_to_from (node_to=?)
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
USING ROWID SEARCH ON TABLE sub_nodes FOR IN-OPERATOR
```
Three-way join:
> ```
EXPLAIN QUERY PLAN SELECT count(edge._rowid_) FROM edge,
temp.sub_nodes sub_from ON sub_from.node_id = node_from,
temp.sub_nodes sub_to ON sub_to.node_id = node_to
```
```
SCAN TABLE sub_nodes AS sub_from
SEARCH TABLE edge USING COVERING INDEX edge_from_to (node_from=?)
SEARCH TABLE sub_nodes AS sub_to USING INTEGER PRIMARY KEY (rowid=?)
```
The runtimes of these two queries remain roughly equivalent with increasing problem size.