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

On Sat, Mar 27, 2021 at 8:07 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

The optimizer is smarter than I thought. He seems to use all these indexes as needed so

SELECT ucword FROM ucwrdlstpf WHERE ucword LIKE 'Q__AS%'

gives a result in around three seconds. :-O This is *a lot* less than before, with some dozen seconds before. (Three seconds might seem much, but remember, we're talking about a 150 here.)

Well, the normal index handles constants in the leftmost characters.
Earlier in the thread, you said it was slow when you had a wildcard at
the beginning, which is to be expected.

Index:
CREATE INDEX QTEMP/MASTER01 ON CUSTMAST (substring(NAME,1,1))
CREATE INDEX QTEMP/MASTER02 ON CUSTMAST (substring(NAME,2,1))

...and so on, 18 more times.

I did that with DDS (SST statement) according to a private message. I didn't know that partial indexes were possible at all.

I would be very surprised if the substring(NAME, 1, 1) index made any
difference, as long as you already had an index on the full NAME
field.

What would be impressive to me is if those substring indexes helped
significantly when searching for LIKE '%PATTERN%' (variable-length
wildcard at the front and back). I mean, I can *just barely* imagine
how they *might* be helpful for that case.

John Y.