0

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?

4
  • I don't mean to be smart, but why don't you test this on your own system (hardware, tables and rdbms)? Your quote But I guess this can be estimated from any SQL based database? does not hold generally at all. Various systems use different types of BTree and there are various balancing algorithms. Add to that differences in optimiser code and different systems will have different thresholds for choosing different joining algorithms... Quite frankly, hic sunt leones!
    – Vérace
    Oct 2, 2022 at 8:33
  • @Vérace thanks for your hint! I am by no means a database expert, just starting diving into it and basically everything you wrote after 'at all' is new to me. I thought by coming here and asking this question I might get some more insights into this whole topic.
    – four-eyes
    Oct 2, 2022 at 8:40
  • Be prepared to do lots of searching and reading of technical blogs/papers - it's a geek's paradise! :-)
    – Vérace
    Oct 2, 2022 at 8:48
  • @Vérace Thats why I like it :)
    – four-eyes
    Oct 2, 2022 at 19:54

1 Answer 1

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.

6
  • 1
    I like the idea of JOIN. I have to check if that is feasable on a mobile device. thanks!
    – four-eyes
    Oct 2, 2022 at 19:56
  • @four-eyes JOINs are totally allowed, syntactically valid, and just fine performance-wise in SQLite on a mobile device. I do them regularly. (Again, assuming you're tables are indexed properly.)
    – J.D.
    Oct 2, 2022 at 20:50
  • Yes, I know that. But the data in the IN clause comes from a MapView, which means the user pans, drags and zooms around quickly... Thus with every MapView change I need to delete the old data from the table, insert the new data into the table, (calcualte the index?) and then perform the JOIN.
    – four-eyes
    Oct 3, 2022 at 18:23
  • @four-eyes Not sure I fully follow. Everytime you'd have to create a temp table of ids to run the query is the same amount of times you would've had to generate the IN clause query and run it. If it were me, creating the temp table and running the JOIN query would be part of the same query batch. Creating a temp table is a trivial thing.
    – J.D.
    Oct 3, 2022 at 18:32
  • You understand it right. I was just thinking loudly, being not sure if DELETING prev. entries from the table, INSERTING new ones, creating the INDEX and the performing a JOIN would be faster than generating the IN clause query (or overcoming the performance issues of the IN clause query)
    – four-eyes
    Oct 4, 2022 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.