Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

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

 
Reply
   X-Cart forums > News and Announcements
 
Thread Tools
  #1  
Old 06-10-2013, 11:31 PM
 
aim aim is offline
Advanced Staff Users
 

X-Cart team
  
Join Date: Dec 2008
Posts: 928
 

Default 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:
  1. ▒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)
  2. 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.
  3. 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.
  4. the search results will be limited to 1000 ( also see Pros above, #2)
  5. 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!
__________________
Sincerely yours,
Ildar Amankulov
Head of Maintenance group
Reply With Quote

The following user thanks aim for this useful post:
davidjones_01 (06-21-2013)
  #2  
Old 06-11-2013, 03:44 AM
 
carpeperdiem carpeperdiem is offline
 

X-Guru
  
Join Date: Jul 2006
Location: New York City, USA
Posts: 5,399
 

Default 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
__________________
xcart 4.5.4 gold+ w/x-payments 1.0.6; xcart gold 4.4.4
Reply With Quote

The following 2 users thank carpeperdiem for this useful post:
aim (06-30-2013), Ksenia (06-11-2013)
  #3  
Old 06-11-2013, 05:10 AM
 
exsecror exsecror is offline
 

X-Wizard
  
Join Date: Apr 2007
Posts: 1,284
 

Default 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
Reply With Quote

The following 2 users thank exsecror for this useful post:
aim (06-30-2013), Ksenia (06-11-2013)
  #4  
Old 06-11-2013, 05:20 AM
  Ksenia's Avatar 
Ksenia Ksenia is offline
 

X-Cart team
  
Join Date: Apr 2013
Posts: 735
 

Default 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
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


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.
__________________
X-Cart team
Reply With Quote
  #5  
Old 06-11-2013, 05:30 AM
 
carpeperdiem carpeperdiem is offline
 

X-Guru
  
Join Date: Jul 2006
Location: New York City, USA
Posts: 5,399
 

Default 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
__________________
xcart 4.5.4 gold+ w/x-payments 1.0.6; xcart gold 4.4.4
Reply With Quote
  #6  
Old 06-11-2013, 05:38 AM
 
exsecror exsecror is offline
 

X-Wizard
  
Join Date: Apr 2007
Posts: 1,284
 

Default 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.
Reply With Quote
  #7  
Old 06-11-2013, 05:57 AM
  Ksenia's Avatar 
Ksenia Ksenia is offline
 

X-Cart team
  
Join Date: Apr 2013
Posts: 735
 

Default 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
__________________
X-Cart team
Reply With Quote
  #8  
Old 06-11-2013, 06:10 AM
 
carpeperdiem carpeperdiem is offline
 

X-Guru
  
Join Date: Jul 2006
Location: New York City, USA
Posts: 5,399
 

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

Quote:
Originally Posted by Ksenia
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

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.
__________________
xcart 4.5.4 gold+ w/x-payments 1.0.6; xcart gold 4.4.4
Reply With Quote
  #9  
Old 06-11-2013, 06:47 AM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default 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.
Reply With Quote

The following user thanks Jon for this useful post:
aim (06-30-2013)
  #10  
Old 06-11-2013, 07:49 AM
  cflsystems's Avatar 
cflsystems cflsystems is offline
 

Veteran
  
Join Date: Apr 2007
Posts: 14,189
 

Default 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
__________________
Steve Stoyanov
CFLSystems.com
Web Development
Reply With Quote

The following user thanks cflsystems for this useful post:
aim (06-30-2013)
Reply
   X-Cart forums > News and Announcements


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 12:25 AM.

   

 
X-Cart forums © 2001-2020