For Workes & Pages, what is the name of the domain?
What is the issue or error you’re encountering
Weird # of read rows on SELECT queries against table with composite primary keys, and LIMIT increases read row count
What steps have you taken to resolve the issue?
Tested single primary key vs composite with a few queries.
What are the steps to reproduce the issue?
Testing out D1 for a new application and finding there is some weird count for row reads on queries.
create table test_table
(
id integer,
secondary integer,
primary key (id, secondary)
);
insert into test_table (id, secondary)
values (1, 1),
(2, 2),
(3, 1),
(4, 2),
(5, 1),
(6, 3);
Eg. this query returns 6 rows read, though removing the limit takes it down to 4. Regardless there is only one row in the DB with id=6, so it should hit the index and only require one row read to my understanding.
SELECT * FROM test_table WHERE id=6 and secondary in (1,2,3,4,5) LIMIT 3
``
Query plan for that statement doesn't so anything obvious, `SEARCH test_table USING COVERING INDEX sqlite_autoindex_test_table_1 (id=? AND secondary=?)`. And the fact that adding limit *increases the rows read by 2* if very confusing to me, though I don't understand the underlying options enough.
Additionally, if I remove a row, so there are only 5 rows in the table, the rows read are still 6. Though forcing 'NOT INDEX' forces a normal table scan and result in the size of the table 5 in that case, regardless of limit clause. It's interesting that an index scan results in more row reads than a full table scan, more rows than are in the table. Locally, I even created an imposter table and verified that the index only contains the 5 rows, so not sure how it's reading 6 or 4 without the limit clause.
Very weird behavior, but composite keys are needed for my app design, so kind of makes D1 off the table at this point if I can expect huge unexpected read amplification. Especially where I assume is a B-tree index apparently increases reads