Keith,
The final script produces corresponding results here, only a constant factor
slower (minimal CPU). The rows per second is useful to summarize the tests for
various keyset sizes. Below is the average per method with input parameter 5.
meth|rps|note
1|149431|Individual Row
On Monday, 16 September, 2019 14:22, E.Pasma wrote:
>Stop stop stop
>> create table x
>> (
>>id integer primay key,
>>datablob
>> );
>I did not see this until searching for the word PRIMARY and not finding
>it. Thus id is not a primary key at all. Probably it is a good habit
Stop stop stop
> create table x
> (
>id integer primay key,
>datablob
> );
I did not see this until searching for the word PRIMARY and not finding it.
Thus id is not a primary key at all. Probably it is a good habit to always add
WITHOUT ROWID when there is an explicit primary
Keith Medcalf, on Monday, September 16, 2019 01:33 PM, wrote...
>
> It will, but that depends how many rows there are.
>
> That is, the statement: SELECT * FROM t1 WHERE id IN (1,2,3,4,5,6)
>
> Is equivalent to
>
> CREATE TEMPORARY TABLE keyset (key PRIMARY KEY);
> INSERT OR IGNORE INTO keyset
It will, but that depends how many rows there are.
That is, the statement: SELECT * FROM t1 WHERE id IN (1,2,3,4,5,6)
Is equivalent to
CREATE TEMPORARY TABLE keyset (key PRIMARY KEY);
INSERT OR IGNORE INTO keyset VALUES (1), (2), (3), (4), (5), (6);
SELECT * FROM t1 WHERE id IN keyset;
DROP
On 16 Sep 2019, at 5:58pm, Jens Alfke wrote:
> Experimentally, the optimizer seems to choose an index search even with the
> simpler query. I ran this on a test database with about 30k rows.
In case you forgot I'm just reminding you to run ANALYZE after putting your
data and indexes in.
> On Sep 13, 2019, at 10:57 AM, Hick Gunter wrote:
>
> This is faster if the number of keys in the list is small relative to the
> number of records in the table.
> If the number of keys is similar to the number of records in the table, then
> a simple full table scan may be faster.
WITH list (key) AS (VALUES (mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jens Alfke
Gesendet: Freitag, 13. September 2019 18:39
An: SQLite mailing list
Betreff: [EXTERNAL] [sqlite] Fastest way to SELECT on a set of keys?
If I have a set of primary keys (let's say a few
8 matches
Mail list logo
