Given that LIKE with ESCAPE is not currently optimized via index for prefix searches (https://github.com/cockroachdb/cockroach/issues/30192), what is the best way to search for a key with a given prefix in case prefix is only known at runtime?
I us LIKE against prefix + ‘%’, but I’m not able to escape ‘_’ and ‘%’ in the prefix itself, which makes this search incorrect.
LEFT(key, 3) = 'abc' is also not optimized.
I could do something like
key >= <prefix> AND key < <next of prefix>, where
<next of prefix> would be upper bound for the range (for example, it will be ‘abd’ for prefix ‘abc’), but computing such string could be error-prone (especially, given UTF-8 – not sure what’s the correct way to do it!).