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▓

   X-Cart forums > News and Announcements
Thread Tools
Old 06-11-2013, 08:20 AM
  ADDISON's Avatar 

Join Date: Jan 2008
Posts: 2,613

Default 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.
X-Cart Next: Business 5.2 (learning and testing)
X-Cart Classic: Gold and Gold Plus 4.7
Lots of Modules and Customizations
OS in use: Red Hat Enterprise, Fedora, CentOS, Debian, Ubuntu, Linux Mint, Kali Linux
Ideas for Server configuration (basicaly): Nginx/Pound (reverse proxy), Apache/Nginx (webserver), Squid/Varnish (cache server), HHVM or (PHP-FPM + PHP 5.6 + opcache), MariaDB/Percona MySQL Server, Redis (storing sessions)

You can catch my ideas here:
Reply With Quote
Old 06-11-2013, 11:40 AM
  Jon's Avatar 
Jon Jon is offline

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

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

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.
Reply With Quote
Old 06-11-2013, 04:56 PM
  wjbrewer's Avatar 
wjbrewer wjbrewer is offline

Join Date: Feb 2005
Location: Pittsburgh, PA
Posts: 504

Default 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.
Reply With Quote
Old 06-30-2013, 08:48 AM
jrsvsp jrsvsp is offline

Advanced Member
Join Date: Oct 2011
Posts: 73

Default 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.
X-Cart 4.4.5 Live
X-cart 4.5.5 Live
Reply With Quote

The following user thanks jrsvsp for this useful post:
aim (06-30-2013)
   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:00 PM.


X-Cart forums © 2001-2020