[WD23] Full text index works no more with partial words!

Hi all,

I wonder what happened to my free product search window using full text index. It used to work OK in WD21, but no longer in WD23; Searches with full words still work, but when trying to search with a partial word and the "*"-wildcard, it now throws an error like:
"The rootinization can be used with a 'starts with' search only.", error code 76006.
This makes this search window practically useless in my language, where conjugation of words is done with postfixes instead of prefixes.

Does anybody know what is going on, and what I should do to fix this problem? Any hints and tips will be greatly appreciated.

best regards
Ola
.

Hi all,

I found the culprit; it's that clever "Stemming based on the language"-option in the full-text index in analysis. I disabled it, and now the search works again like it used before.

Still, I would like to know, how to make that option work in the actual search situation, without generating an error.

Best regards
Ola

Hi Ola,

-IF- I understand correctly, you are trying to use a full text index, which is specially made to return fast result, with a CONTAIN search (which is the slowest possible search, as it requires to read all records and compare inside)...

And it looks like it this illogical case was not controlled before and is now, hence your error.

So, what you can do:
- for any contain search, ditch the full text index and search on the main file, as it will give you the same slow time anyway (the whole file needs to be read)
-OR-
- Code yourself the full text index (you can use my code for that in WXEDM). If you do that, you are in control on how the words are indexed and therefore you can CUT THEM IN PARTS when writing the index. This means that you can write your postfixes independently in the index, and therefore in the end search with an EQUAL condition (ie fast)

Best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Hi Fabrice,

Thanks for the info.

However, my experience of the speed of WD's full text search is quite different from yours. I have a product file (HFSQL classic) with over 6000 records (mostly books). When I do a free search with the * wildcard, say for instance messer*, it will find all my Messerschmitt-related book titles, over 60 items of them, in less than a second, over a LAN! I am very pleased with it.

Then, on the other hand, I also have a very similar free search window on my contacts file, over 13000 records, were the result is also very fast, but totally wrong, practically it does not work at all. It finds the search string only in some fields, but not all. It just skips over several fields. And I can't get it right, no matter what. I've tried to rebuild the FullText_Index a couple of times and re-arrange the sequence of the fields in it, but with no luck. So obviously, when I have the time, I'll have to have a look at your WXEDM to get this one working, too.

Best regards
Ola

Hi Ola,

I don't think that we can talk about speed with only 6000 records... Reading all of them in a query should take less than a second anyway.

Furthermore, doing a search on messer* is NOT the case I was talking about, as it is a fast STARTS WITH condition and it should always work extremely fast if there is any type of index.

The case that was creating the error should be the opposite one (*messer in this case), which is a ENDS WITH or contains...

If you want fast results with that you need to either create a revered index (ressem* in that case) or break up the full word in parts for a better contain search.

Best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Hi Fabrice,

Obviously we are talking about different things, because the "STARTS WITH" and strings is what I was talking.

And I really don't see how a reversed index would help in a case like this. Please correct me if I am wrong, but I understand that a reversed index is mainly useable and improving the results when searching among a large number of sequential numbers, like invoice numbers, and not so much, if at all, with word-strings like "messer".

Best regards
Ola

Hi Ola,

so miscommunication from the start, and it looks like we continue to do that :-)

What I call a reverse index is to create a secondary index with an inverted string in it

So messerschmidt would be recorded in the revered index as tdimhcsressem (if I didn't make a mistake).
In that case, it would allow to find very fast any word ending by schmidt, as the code would do a start with "tdimhcs" on the inverse index.

Best regards

Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

Free Video Courses, free WXShowroom.com, open source WXReplication, open
source WXEDM.

More information on [www.fabriceharari.com]

Hi Fabrice,

That much I understand (what a reversed index is). And I agree on its useability on numeric type keys, especially when there is a large number of them. I am just doubting its useability with string type (word type) keys.

Best regards
Ola

P.S. If my search process found books in stock whose title includes "schmidt", I would probably fire my buyer (= that's me!)