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)

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

Hi folks,

We▓re on the threshold of taking an important decision: change in x-cart search ( standard products search in customer area) is in question.

Why we▓re hesitating:
The сhange is rather expensive (up to 20-30 resource days, depending on realisation). And if the change is not really needed to our community, it▓s better not to waste time and efforts, but to focus our efforts on more important tasks.

What we consider:
Replace the current MySQL LIKE with ▒FULL-TEXT▓ search (or, which is even more resource consuming, add ability for admin to select between these two).

Pros:

Win in speed.

1) The current search query, e.g:

Code:

SELECT * FROM xcart_products

JOIN ...<lots of JOINs>...

WHERE

<lots of other conditions>

AND

product LIKE ''%SKU17574%' or productcode LIKE '%SKU17574%'

<...>

requires 2.38 sec, given the database contains 20000 products and 60000 variants

The ▒Full-Text▓ search will require only 0.09 sec, which is 26.4 times faster.

Moreover, currently ( it will most probably be optimized later), if there are 1500+ products in search result(or in the selected category), the search query is executed twice - first to select products, after that - to count them ( which is needed for page navigator). It means ~30-40% increase in time required.

2) Without 1000 results limitation ( also see Cons below, #3) the ▒Full-Text▓ search will take 0.20 seconds, which is still 11 times faster.

3) The query execution time will depend on substring itself. The more exact the search string, the faster. Say, the results for more specific ■Agatha Christie■ will be returned much faster than the ones for a more general word ⌠book■.

Cons:

the win in speed will be palpable only if the store has many ( from 10K) products
the ▒Full-Text▓ search has some restrictions (http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html) such as

▒Full-Text▓ searches are supported for MyISAM tables only. (In MySQL 5.6 and up, they can also be used with InnoDB tables.), see #12.9.5 of MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html)
stopwords (http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html). The default list of stopwords can be changed providing you have root access and can manage MySQL settings.
minimal number of symbols -4. If the user types less, the results will be empty. ( say, ▒cat▓ search string will return nothing). Also manageable, but with access to MySQL configuration files.
the search results will be limited to 1000 ( also see Pros above, #2)
the search inside the word will not be possible. If the product name is ▒apple▓, the customer who types ▒appl▓ will find something, but the one who types ▒pple▓ will NOT. Can NOT be changed on application or server level at all.
At the same time the search of words inside the phrase ( product ▒green apple■ will be found if you search for either ▒apple▓ or ▒appl▓ or ▒green▓) will keep working.

What we expect from you:

What do you think about this potential change?
If there▓s ability to select between old (LIKE) and new (FULTEXT), which one will you choose?

Please share your ideas, concerns and thoughts about it.

We would like to hear as many opinions as possible - each of them counts!

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

My store has less than 100 products, about 800 variants total.
This will not help me.
I also use CloudSearch, and I am most satisfied with search performance.
I am not a candidate for this project, correct?
If I were in control of X-Cart engineering resources, I would prefer that you fixed a dozen or more long-standing bugs. But that's just my opinion.
Thanks.
J

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

Honestly limiting to 1,000 search results isn't as bad as it sounds. A lot of third party services (Google Commerce Search [note this product is being deprecrated] and SLI for example) do this. We do that with our search results (powered by HawkSearch) because honestly if someone is having to go past 6+ pages in a search for a product you have bigger problems then large result sets.

Note: We have about 152,000 active SKUs

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

Quote:

Originally Posted by exsecror

Honestly limiting to 1,000 search results isn't as bad as it sounds. A lot of third party services (Google Commerce Search [note this product is being deprecrated], SLI and a few others do this). We do that with our search results (powered by HawkSearch) because honestly if someone is having to go past 5+ pages in a search for a product you have bigger problems then large result sets.

Note: We have about 152,000 active SKUs

Thank you for your reply, but could you please clarify:

1) you commented on one limitation only, what about others?
2) would you prefer to use Full-Text search instead of current one, assuming that you can switch here and now ?

Quote:

Originally Posted by carpeperdiem

Thank you, Jeremy. We understand your point - and just like you, we do not want to waste resources. That's why we decided to ask our community. Your and Alan's post actually demonstrate that this is a dilemma indeed.

And we are equally interested in all the opinions, be they for or against.

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

Quote:

Originally Posted by Ksenia

Your and Alan's post actually demonstrate that this is a dilemma indeed.

And we are equally interested in all the opinions, be they for or against.

Maybe this should be a SURVEY (link sent by email) to the entire registered user list?

Make the survey to the point, and 4 or 5 questions maximum.
I get surveys every now and then from other software companies... if it's only a few specific questions, I always try to answer. When companies send 50 question surveys with a dozen multiple-choice must-answer questions, I bail.

Obviously, anything "faster" or "better" is always welcome, but if the technology is only beneficial to stores with >10k skus, then that should be taken into consideration.

Thanks for asking.
Jeremy

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

Quote:

Originally Posted by Ksenia

1) you commented on one limitation only, what about others?
2) would you prefer to use Full-Text search instead of current one, assuming that you can switch here and now ?

Answer into inquiry 1:

I really cannot comment more because again my knowledge and experience with RDBMS fulltext searches is extremely limited due to the availability of other targeted technologies. I merely commented on the fact about the 1,000 search result limit because that shouldn't be a factor, large search result sets are not necessarily better because a larger result set can mean that the search is not finely tuned correctly.

Answer into inquiry 2:

To be honest I don't know enough about how MySQL's FullText search works to give you honest and concise answer. For search we've always used technologies specifically built for it such as Sphinx, Apache Solr, Lucene (note: both Apache Solr and HawkSearch are Lucene based) and Amazon Cloudsearch since when it comes to search having stemming, faceting, synonyms, term vectoring (especially term compression) and etc because as far as I know RDBMS fulltext search doesn't facilitate these (someone correct me if I'm wrong) and that's important in e-commerce search.

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

Quote:

Originally Posted by carpeperdiem

Maybe this should be a SURVEY (link sent by email) to the entire registered user list?

Good idea, but to begin with, let's start from the forum users.

This is a quick form ( 2 questions, your nickname and your opinion about Full-text), will you, our dear merchants and developers, please vote?

https://docs.google.com/forms/d/1uIMlElpYLP5Knj4VRiemFGDU76Kt3Y6ITSQ_v25KXc4/viewform

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

Quote:

Originally Posted by Ksenia

Ksenia, I had no idea GoogleForms existed! Thank you for sharing this cool service. (I always paid for SurveyMonkey, but this is better, and it's free!)
Not sure which answer I should choose, because CloudSearch is my preferred search -- but I don't know how CloudSearch will perform if I have >1000 skus.

If a store is using CloudSearch with >1000 skus, does Full-text matter?

I am ALL FOR doing anything that speeds up the customer experience. CloudSearch for me is working fine and I am not the best person to be having this conversation with, so I will step-aside and simply answer the survey.

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

X-Cart should do a fulltext search and then utilize the relevancy feature so that you can return results that are actually relevant with the most relevant items first. You can also implement boolean operators. This makes much more sense than doing a general like query to get thousands of mostly irrelevant results and then sorting them so the most relevant results could be no where near the top.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

I had created this functionality for X-Cart as early as 2004.

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

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