X-Cart forums - Consider further search optimization, hesitate between MySQL LIKE and ▒FULL-TEXT▓

X-Cart forums (https://forum.x-cart.com/index.php)

- News and Announcements (https://forum.x-cart.com/forumdisplay.php?f=28)

Re: Consider further search optimization, hesitate between MySQL LIKE and ▒FULL-TEXT▓

I read many times this thread and I have to agree it is very hard to take a decision. It's like looking at an apple, you see its color green or red, but you don't know how the taste could be without a bite. In this moment we would like to bite, but in this case some work from your side is needed. Do you think it worth moving a few steps forward? This is the main question.

Before voting, I would like to read some technical posts, understanding more in detail the situation, beyond the pros/cons comparison.

Re: Consider further search optimization, hesitate between MySQL LIKE and ▒FULL-TEXT▓

Quote:

Originally Posted by cflsystems

Anything you can do to increase speed and provide better search in XC is good. As others mentioned some of the limitations are not relevant
- search that returns 5000 results is as bad as the one that returns 1000 - either way I will not go through all these pages;
- if anyone is looking for "apple" and tries to search for it with "pple" deserves not to find anything
- the 4 symbols may be a troubled (as your example "cat") but still not a stopper especially if it can be changed

The way I had coded this before, is that if the term is less than 4 results it would do a like search and 4 or more it would do full text.

Re: Consider further search optimization, hesitate between MySQL LIKE and ▒FULL-TEXT▓

If I had to choose between the two, I would choose a full text search. It is much faster and more provides more relevant results, and would be a modest improvement over the current search function.

However, the cons to this search may be deal breakers. None of four character, stopwords, and other 'cons' can be changed unless you are on a dedicated server. Maybe the largest issue with the full text search, which I don't think has been mentioned, is numbers and special characters do not work very well, and often return very odd results. While a full text search works well with basic queries containing words four alpha characters and greater, no other searches will benefit from this technology, no matter what changes are made to the settings on your server.

For truly relevant results, X-Cart needs a custom engine that takes into account word density, position, section, stemming, and incorrect spelling. This is what I had to do with the Smart Search, it was the only option that worked.

Re: Consider further search optimization, hesitate between MySQL LIKE and ▒FULL-TEXT▓

While a better search sounds a good idea I don't like the restrictions on full text. As many stores operate on shared server environments we cannot change the stop words and I am also concerned about the comments on numbers and 3 character search arguments (if I understand correctly), so I think if this is done it needs to be admin selectable.

Personally I think the effort could be better spent elsewhere.