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 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:
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:
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:
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:
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:
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:
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:
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 |
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:
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. |
All times are GMT -8. The time now is 08:46 PM. |
Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.