If you are looking to determine if string B is contained in string A, then the correct test is: INSTR(A, B) > 0 not A LIKE ('%' || B || '%')

The purpose of LIKE is entirely different. LIKE does 47 things, of which you only need 1, and that 1 thing that you are trying to accomplish is the one (and only) function of the INSTR function.

If you insist on using LIKE then you will probably have to deal with the other 46 things that LIKE does that you need to kaibosh. Good luck with that.

Actually, I use 3 things FROM LIKE. The other 2 being case-insensitivity (trivially possible if I used LOWER before INSTR though) and wildcards (I already use them, and will likely use more in the future). LIKE is the most natural fit in this case, I'd say.

That said, I might still be able to use INSTR and a slightly more complex setup (I think all I'll ever need from LIKE is '%', and I can cope with INSTR(TextColumn, String1A) AND INSTR(TextColumn, String1B) sometimes having a different result than `TextColumn LIKE '%String1A%String1B%').

Well, scratch the INSTR idea. When I redo the query and index to use INSTR, SQLite still has the problem where it can't figure out it can use the index.

Just out of interest, what is the performance difference between using SQLite's chosen query plan, or that forced by the use of INDEXED BY?

For just the sub-query, about a 9x improvement. For the original version of the overall query I was testing, about 4x. For a faster version I stumbled into while testing out stuff after posting, only about a 2x improvement.

Is this a bug to be fixed, or a case where the documentation's warning against using INDEXED BY to optimize queries should be ignored?

I remember being told by DRH in a tutorial that LIKE doesn't use the column's index if the pattern has a wildcard at the beginning because indices are sorted from the beginning (because sorting from the middle makes no sense). This would seem to be the issue here. (I also believe that REGEXP never uses indices, as SQLite doesn't spend a lot of effort on analysing regular expressions.)

If you're doing many more of these queries than you are doing alterations to the set of queries (and given that the set of queries is actually very small), it might be worth creating another table that says for each row which of the queries it matches. That can be done ahead of time and indexed, and lookups against that should be very fast. Even keeping things up to date when the data in the main table changes should be pretty easy; it's only altering the set of special queries that will be expensive.

The initial wildcard wasn't the problem here. What I've finally caught on to is the fact I was using the index slightly differently than normal: WHERE Col LIKE 'constant' OR Col LIKE 'constant2' etc; where as normally you do WHERE IndexedCol='whatever'. Once I switched to WHERE (Col LIKE 'constant' OR etc.)=1, SQLite was immediately able to start using the index voluntarily.

I did a little more testing as well, and all function indexes I tried (INSTR with a constant, squaring a column, and a custom function) all had the same problem if I excluded an '=Whatever' part. Given this means I was essentially trying to use an index for a <expr>!=0 term, which also doesn't work, then it makes sense.

I wonder if it would be possible for SQLite to be changed to make it work without having to use (<expr> OR <expr> OR etc.)>0 for the simpler and (to me) more intuitive syntax. However, I fully expect that to not be as simple of a change as it sounds, and therefore not happen.

Post SQL to reproduce your issue and solution and many more might spend time on it...

Philip

Not only is this thread already resolved (barring the unlikely case where SQLite being enhanced in the way I described in my last post), I posted the snippet of what I believed to be the culprit in the first post and was ultimately proven correct.