Unable to reproduce. In particular: >SELECT * FROM nodes WHERE SUBSTR(name, 0, ?) = ? >tells me that it can and will use the (primary key) index on the name >column.
will not use the index. I can make it use an index by doctoring the table data to make the index scan cheaper than a table scan, but it is still doing a table scan, just doing it by scanning the index because it is cheaper. SUBSTR(name, 0, ?) is an expression, so unless you have an index on that expression, then an index cannot be used to SEARCH for the rows. You would have to use a constant for the length, however, not a parameter. Nonetheless, an index on the entire column may be used to SCAN if scanning the index is cheaper than scanning the table. >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. Because you are still having to scan the entire table, whether directly, or by scanning an index that starts with the name column. Of course, without knowing the definition of the table nodes, it is pretty difficult to know anything at all, since there are quite a lot of dependencies on the declaration of the table (is it a rowid table, a without rowid table, is name the primary key or merely a unique index, and what is the collation of name). >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. Exactly so. The rules for when an index can be used reveals that an index may be used when the various conditions are met, including the condition that the RHS of the LIKE is a bound parameter or constant text that does not start with a '-' or a number. An expression is neither a parameter nor constant text so the LIKE optimization will not be attempted. https://sqlite.org/optoverview.html#the_like_optimization You could always simply perform the concatenation in your application and use the like operator everywhere. Then you could be sure that a range scan of the applicable index would always be used provided that the parameter does not start with a '-' or a number (or a wildcard). -- 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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Brannon King >Sent: Tuesday, 8 October, 2019 15:53 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] SELECT uses index with SUBSTR but UPDATE doesn't > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
