I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause.
UPDATE tbl1 SET col1 = "Y" WHERE EXISTS (SELECT NULL FROM tbl2 WHERE tbl1.col2 = tbl2.col2) AND tbl1.col3 IN (1,2);
- tbl1 has ~4.5M rows. When filtered above by col3, it comes down to ~400k rows.
- tbl2 has ~100k rows. All rows are unique on col2, and every row in tbl2 has a match in tbl1.
- tbl1 has an index on col2 and an index on col3.
- I am using sqlite version 3.29.0 (from Python 3.7.2)
When I run this query without an index on tbl2.col2, the query never completes (I let it run for multiple minutes). If I add a unique index on tbl2(col2), the query finishes almost immediately.
This is confusing to me because I would think that the query should be constrained by the lookup into tbl1, since I'm using an EXISTS query for tbl2 (rather than IN()). The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it, since every row in tbl2.col2 is unique.
Why is the performance so slow without that unique index?