Add option to use full-text catalog for search

Post feature requests to this forum and a pre-configured poll will automatically be created for you.
Post Reply

How important is this enhancement to you?

It's a critical enhancement that I must have.
18
75%
It's an important enhancement but others are more critical.
5
21%
I'd like to have it but it's not that important.
0
No votes
I'd never use this feature.
1
4%
 
Total votes: 24

zafu
Ensign (ENS)
Ensign (ENS)
Posts: 12
Joined: Fri Sep 19, 2008 9:32 am
Location: Westlake, OH
Contact:

Add option to use full-text catalog for search

Post by zafu » Wed Oct 22, 2008 9:27 am

I just modified the api source and using full-text catalog for search is amazingly fast for our large product catalog (400,000 products-120 categories). Results return nearly instantaneously. Searches before always timed-out (over 30 seconds), maxing out our production sql server's HDD.

We're hosting on our own servers so building the full-text catalog and populating on a schedule isn't a problem--I don't know about shared hosting plans. I'd like to see it built-in so others can experience benefits (and to have less code to merge w/ AC updates).

howardthai
Ensign (ENS)
Ensign (ENS)
Posts: 20
Joined: Wed Oct 22, 2008 4:46 pm

Re: Add option to use full-text catalog for search

Post by howardthai » Wed Oct 22, 2008 4:54 pm

Hi, i was reading your post about your full text index having to be 1 mb smaller than the Original one. Is there any problems with yours. Can you share the code for you full text index? im intested in trying it out as well. I also host my own website . http://www.unitedintegral.com

howard

zafu
Ensign (ENS)
Ensign (ENS)
Posts: 12
Joined: Fri Sep 19, 2008 9:32 am
Location: Westlake, OH
Contact:

Re: Add option to use full-text catalog for search

Post by zafu » Thu Oct 23, 2008 10:11 am

Here are steps I took...

1. In ac database created full-text catalog and added full-text indexes to following tables:columns

ac_Categories: Name, Summary
ac_WebPages: Name, Summary
ac_Links: Name, Summary
ac_Products: Name, Summary, sku, searchKeywords, Description, ExtendedDescription

2. In CommerceBuilder solution search code for "LIKE" and change those queries to use "FREETEXT"
e.g.
WHERE P.Description LIKE @keyword
changed to
WHERE FREETEXT(P.Description, @keyword)

*or use CONTAINS command for advanced searching

3. In those methods where I changed query to use freetext I commented out line that added LIKE's wildcard character '%' around the keyword string

4. I also modified the basic search to search SKU, description and extended description. I'm guessing AC doesn't search those fields because it would be slower w/o full-text catalog.


Note: you need to buy the API source code to change the queries in AC search controls or I suppose you could create your own search controls and bypass AC's. We bought source because our large catalog chokes AC in other places too like creating xml sitemaps and product feeds and managing product catalog in admin.

Mike718NY
Commodore (COMO)
Commodore (COMO)
Posts: 485
Joined: Wed Jun 18, 2008 5:24 pm

Re: Add option to use full-text catalog for search

Post by Mike718NY » Fri Feb 06, 2009 11:13 am

Is Full-Text Search available now?
I'm going to convert an old site to AC7 but can't until I have this feature.
Please let me know the status. Thanks

sdlong02
Lieutenant Commander (LCDR)
Lieutenant Commander (LCDR)
Posts: 95
Joined: Mon Jan 19, 2009 2:33 pm

Re: Add option to use full-text catalog for search

Post by sdlong02 » Tue May 12, 2009 4:52 pm

This is an expected upgrade in 7.0.3

viewtopic.php?f=42&t=10278

User avatar
Logan Rhodehamel
Developer
Developer
Posts: 4116
Joined: Wed Dec 10, 2003 5:26 pm

Re: Add option to use full-text catalog for search

Post by Logan Rhodehamel » Fri Jun 12, 2009 1:06 pm

In AC703 (released 1 June 2009) the store can make use of FTS for product searches.
Cheers,
Logan
Image.com

If I do not respond to an unsolicited private message, it's not because I'm ignoring you. It's because the answer to your question is valuable to others. Try the new topic button.

Post Reply