We have started using queries of the form SELECT ... WHERE ... AND local_relpath LIKE ...
and I was curious about the performance of LIKE. So I tried a large database: $ sqlite3 wcx.db "select count(*) from nodes" 377021 and a LIKE query: $ time sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath = 'zig1/zag27' or local_relpath like 'zig1/zag27/%' escape '%'" 101 real 0m0.398s user 0m0.348s sys 0m0.048s Using escape adds a bit to the time, as does the double check on local_relpath caused by '/%' instead of '%', but both of those are small effects. A suggested optimisation is $ time sqlite3 wcx.db "select count(*) from nodes where wc_id = 1 and local_relpath >= 'zig1/zag27' and local_relpath < 'zig1/zag28'" 101 real 0m0.005s user 0m0.004s sys 0m0.000s I checked using "select *" and the two queries return the same data. Can we reliably calculate the "next string" for a given UTF-8 string? That is 'zig1/zag28' given 'zig1/zag27'? Can we treat the string as a bytes and just increment and carry? -- Philip
