Fascinating ... because the optimal query plan would be to do a table scan of tbl1 and then dip into tbl2 for each row to get the update value. The only meaningful index then would be on tbl2(int) (that is idx3) since an index will be unhelpful for the LIKE constraint.
Is the generated code different for the two queries, the one that runs slow -vs- the one that runs fast? (That is, the output from EXPLAIN ..., not the output from EXPLAIN QUERY PLAN, or from .eqp full in the CLI). Also, what version of SQLite3 are you using? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of dirdi >Sent: Thursday, 29 August, 2019 11:57 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Unexpected REINDEX behavior. > >On 8/29/19 6:10 PM, Keith Medcalf wrote: >> Do you have sqlite_stat1 or sqlite_stat4 tables present (that is, >have you ever run analyze)? > >No. > >> If the sqlite_stat1 or sqlite_stat4 tables are present, then if you >run ANALYZE; rather than reindexing or dropping/recreating the index, >what is the result? > >If I run ... > >> ANALYZE; >> REINDEX; >> ANALYZE; > >... instead of ... > >> DROP INDEX `idx1`; >> CREATE INDEX `idx1` ON `tbl1` ( >> `int` >> ); > >the UPDATE query remains being slow (~36m). > >-- >Best regards > >dirdi >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
