Performance Quetions. Search database with range query or with and id

Question

I have a database, consisting of the following columns

• id, a string looking like this 8b28347448d3fff (15 length)
• x, a decimal (8,6)
• y, a decimal (9,6)

All the columns have Indexes on them. Now, I wan to find matching pairs. On the table side foo, there can be up to 300k of rows. There are two ways to query the table I can think of. First, this one:

Using a WHERE ... IN. On the query side, there might be up to 11k elements in possible_matching_indexes.

SELECT id FROM foo WHERE id IN (possible_matching_indexes);

Another one would be this, which would only result in four values (x1, x2, y1, y2) on the query side

SELECT id FROM foo WHERE (x BETWEEN x1 and x2) AND (x BETWEEN y1 and y2);

Which one is more likely to more performant? I am using a SQLite database. But I guess this can be estimated from any SQL based database?

Answer 1

The IN clause generally is known to be able to cause cardinality estimation issues under certain circumstances (typically with larger lists of items) among multiple database systems. I can't speak for SQLite specifically, but 11k is a lot of items to stuff in one IN clause regardless. The IN clause is logically similar to a bunch of ORs.

If those were your only two options I'd vote for the range filter. But there's a third option that I prefer best. Since you seem to know the ids already of the 11k items you want, you should put them in a temporary table, and then join that table to your actual table, foo, by the id field. This is a more relational approach that generally works better over large lists in an IN clause.

Of course, any of the solutions mentioned are dependent on your table foo being properly indexed. And like Verace mentioned, the best way to find out is to just test, as there's a lot of granular factors that can affect performance. The aforementioned advice is just general guidance.