[aim]

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:

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

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!