I have a database, consisting of the following columns
id, a string looking like this
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:
WHERE ... IN. On the query side, there might be up to 11k elements in
SELECT id FROM foo WHERE id IN (possible_matching_indexes);
Another one would be this, which would only result in four values (
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?
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!