Skip to main content
D
Dax_Trajero
Inspiring
August 15, 2011
Answered

implementing a search box - best practices

  • August 15, 2011
  • 1 reply
  • 771 views

I'm implementing a simple search box, to allow visitors to search for merchandise, which is held in a table. I can see two main approaches, each with their pro's and cons:

:

The merchandise data has several fields that could be potentially employed in the search. long description, short description and title.

A thorough search would look through each long description field, which is 100 chars long. The downside being the speed hit, searching such a large field.

A quick search would look through the title field - quick but not thorough

Alternatively I could create a separate table, searchTags, which contains a list of keywords for each item of merchandis - quicker but not as thorough

Just wondering what type of apporach people use ?

    This topic has been closed for replies.
    Correct answer JMF3

    Would agree with Adam at that scale:

    Just do a bunch of LIKE clauses (except with CFQUERYPARAMS, of course):

    WHERE (

    Title LIKE '%#form.searchTerm#%'

    OR ShortDescription LIKE '%#form.searchTerm#%'

    OR LongDescription LIKE '%#form.searchTerm#%'

    )

    Unless there's something wrong with your DB server, I think you'll be

    surprised at how fast that is given the small amounts of data involved.

    1 reply

    A
    Adam Cameron.
    Inspiring
    August 15, 2011

    100chars is not long. 100chars is closer to "nothing" than it is to "a meaningful amount of data" in the bigger scheme of things.  Even if you ran a poxy DB like Access.

    You could do a LIKE filter on each of the three columns in question, or you could concatenate the three together and do a single LIKE filter on that, or you could look at full-text indexing.

    You could tag things with keywords too (as per your last suggestion) if you think that would help, or if you had enough data per category / tag for it to be a useful filter criteria.

    How many records are you talking about here?

    --
    Adam

    D
    Dax_TrajeroAuthor
    Inspiring
    August 15, 2011

    1,800 records ?

    Didn't know I could concatenate 3 fields into one - will look into that one.

    J
    JMF3Correct answer
    Participating Frequently
    August 15, 2011

    Would agree with Adam at that scale:

    Just do a bunch of LIKE clauses (except with CFQUERYPARAMS, of course):

    WHERE (

    Title LIKE '%#form.searchTerm#%'

    OR ShortDescription LIKE '%#form.searchTerm#%'

    OR LongDescription LIKE '%#form.searchTerm#%'

    )

    Unless there's something wrong with your DB server, I think you'll be

    surprised at how fast that is given the small amounts of data involved.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices