For a query
SELECT * FROM plain_note WHERE id IN (1, 2, 3, ...);
For the filter id array, I was wondering, is there any limitation on the input array length?
Let's say the size of array is 10,000 items, as fast as speed performance is concerned, is it better to perform using IN clause, or is it better to perform a multiple query like
SELECT * FROM plain_note WHERE id = 1;
SELECT * FROM plain_note WHERE id = 2;
SELECT * FROM plain_note WHERE id = 3;
SELECT * FROM plain_note WHERE id = ...;
I couldn't find anything in the documentation on WHERE IN (...), but the length of a SQLite statement itself has a max limit.
However, to your second question, the WHERE id IN (1, 2, 3, ...) version is far preferable to doing separate queries for each id value, for so many reasons. The biggest reason is that the former WHERE IN approach requires making only one rountrip to and from SQLite. Note that your first version is identical to this:
SELECT * FROM plain_note WHERE id = 1 OR id = 2 OR id = 3 OR ...
See:
There seems to be no limit for number of parameters in the IN-clause, But the maximum length of the query might be limiting here.
Converting and executing the IN-clause as multiple SELECT statements is VERY SLOW!
An alternative would be to insert the parameters into a temporary table and to query with
SELECT *
FROM plain_note
WHERE id IN (SELECT id FROM temp_table);