Re: SQL on V4: Index Optimization (Hobbyist question)

On Sun, Mar 28, 2021 at 7:03 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Problem solved. Thanks a lot for great your help and valuable input!

Wrong! I used the wrong test string. It should have been

SELECT ucword FROM ucwrdlstpf WHERE ucword LIKE '______C_U%'

And indeed, with that and the per-char indexes, a full-table scan happens.

SQL optimizers have never struck me as very clever. They do
straightforward, obvious things, and do them very well, sometimes
freakishly well. But they are not ingenious at all. Not even a little
bit. (Maybe these days there are cutting-edge,
machine-learning-powered query engines that are changing this?)

In principle, if you have an index on the 7th position, you should be
able to quickly find all the rows with 'C' in that position. Surely
that would eliminate the need for scanning the whole table. You can
easily write a simple RPG program to do it, given such an index,
right?

So a mix of single-char wildcards and constants could, in theory, be
sped up significantly by those substring indexes. If the query engine
isn't clever enough to figure that out on its own, perhaps there is a
way to rewrite your query to make it clearer. Or maybe there isn't, I
don't know. (I have often written queries that *I* think should be
extremely obvious to the query engine how to make use of indexes, but
it stubbornly refuses to use them.)

Variable-length wildcards are another matter. They are inherently harder.

John Y.