I have this query: UPDATE nodes SET parent = ? WHERE SUBSTR(name, 0, ?) = ? EXPLAIN QUERY PLAN tells me that it is going to do a table scan. At the same time, the query plan for this: SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? tells me that it can and will use the (primary key) index on the name column.
With that info, I thought that this query would be faster: UPDATE nodes SET parent = ? WHERE name IN (SELECT name FROM nodes WHERE SUBSTR(name, 0, ?) = ?) Alas, it's not. I don't know why. UPDATE will use the index if I use the LIKE operator. However, it won't use the index if I attempt LIKE (? || '%'). Whatever handles the string concatenation breaks the use of the index. I don't want to have to sanitize my own data. I have very arbitrary, user-entered, malicious data going in. It's also not clear to me what the sanitizer does for the LIKE operator. What does it do to existing percent signs in the data? I don't want to use those as wildcards. Hence, I much prefer the SUBSTR approach; it seems much safer all around. I run v3.29.0. I hope this can prompt somebody to make the SUBSTR operator work with the indexes on an UPDATE statement. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
