Professional Web Applications Themes

designing keyword searches, LIKE, MATCH AGAINST, etc... - MySQL

Hi I have a table "Item" with 5 varchar columns id, factory, type, category, section i currently am using LIKE queries to generate keyword search results SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo bar%') ... etc. However it was suggested to me to use the MATCH() function so I could get relevance scoring. So I did ALTER table Item ADD FULLTEXT (id, factory, type, category, section) however when I do a search with the MATCH query 'fo' doesn't match, although 'foo' will. Is there a way to combine the MATCH query with the LIKE ...

  1. #1

    Default designing keyword searches, LIKE, MATCH AGAINST, etc...

    Hi I have a table "Item" with 5 varchar columns

    id, factory, type, category, section

    i currently am using LIKE queries to generate keyword search results

    SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo
    bar%') ... etc.

    However it was suggested to me to use the MATCH() function so I could
    get relevance scoring.

    So I did
    ALTER table Item ADD FULLTEXT (id, factory, type, category, section)

    however when I do a search with the MATCH query 'fo' doesn't match,
    although 'foo' will.

    Is there a way to combine the MATCH query with the LIKE so that 'fo'
    would match with the 'foo' entry and then a relevance score would be
    generated?

    Thanks

    ron1972 Guest

  2. #2

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    ron1972 wrote:
    > Hi I have a table "Item" with 5 varchar columns
    >
    > id, factory, type, category, section
    >
    > i currently am using LIKE queries to generate keyword search results
    >
    > SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo
    > bar%') ... etc.
    >
    > However it was suggested to me to use the MATCH() function so I could
    > get relevance scoring.
    >
    > So I did
    > ALTER table Item ADD FULLTEXT (id, factory, type, category, section)
    >
    > however when I do a search with the MATCH query 'fo' doesn't match,
    > although 'foo' will.
    >
    > Is there a way to combine the MATCH query with the LIKE so that 'fo'
    > would match with the 'foo' entry and then a relevance score would be
    > generated?
    >
    > Thanks

    Fulltext indexing is rarely the best idea IMO, and the relevance ordering
    can usually be replaced with a good random number generator.

    I use LIKE and then write my own algorithm for sorting in my language of
    choice if relevance is important.


    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  3. #3

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    "ron1972" <ron1972> wrote in message
    news:1139256326.628219.128560f14g2000cwb.googlegr oups.com...
    > Is there a way to combine the MATCH query with the LIKE so that 'fo'
    > would match with the 'foo' entry and then a relevance score would be
    > generated?
    Nope; fulltext indexing in MySQL doesn't match two-letter strings.

    Using MySQL's fulltext indexing is very tricky. I haven't found a situation
    in which I would employ it.

    I'm not a fan of keyword searching in general. It's almost always better
    from a performance and coding standpoint to categorize entries. Create
    additional tables listing values for given categories, and many-to-many
    tables mapping category values to records in your Item table. Then give
    users a user interface to choose values from which you can filter by
    categories simply by doing JOINs.

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    Brian Wakem wrote:
    > ron1972 wrote:
    >
    >> Hi I have a table "Item" with 5 varchar columns
    >>
    >> id, factory, type, category, section
    >>
    >> i currently am using LIKE queries to generate keyword search results
    >>
    >> SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo
    >> bar%') ... etc.
    >>
    >> However it was suggested to me to use the MATCH() function so I could
    >> get relevance scoring.
    >>
    >> So I did
    >> ALTER table Item ADD FULLTEXT (id, factory, type, category, section)
    >>
    >> however when I do a search with the MATCH query 'fo' doesn't match,
    >> although 'foo' will.
    >>
    >> Is there a way to combine the MATCH query with the LIKE so that 'fo'
    >> would match with the 'foo' entry and then a relevance score would be
    >> generated?
    >>
    You can change the minimum length of words supported by full-text indexes (default : 4)
    [url]http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html[/url]
    You can also use
    AGAINST("fo*" IN BOOLEAN MODE)
    >> Thanks
    >
    >
    > Fulltext indexing is rarely the best idea IMO, and the relevance ordering
    > can usually be replaced with a good random number generator.
    >
    > I use LIKE and then write my own algorithm for sorting in my language of
    > choice if relevance is important.
    >
    >
    That looks like a horrible strategy.
    If you have just a few thousand records, you can cope with LIKE, and client
    side processing, but when your data becomes huge, FULLTEXT is the way to go.
    Searching with FULLTEXT can be up to twenty times faster than LIKE
    (and usually more precise).

    ciao
    gmax


    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  5. #5

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    Giuseppe Maxia wrote:
    > Brian Wakem wrote:
    >> ron1972 wrote:
    >>
    >>> Hi I have a table "Item" with 5 varchar columns
    >>>
    >>> id, factory, type, category, section
    >>>
    >>> i currently am using LIKE queries to generate keyword search results
    >>>
    >>> SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo
    >>> bar%') ... etc.
    >>>
    >>> However it was suggested to me to use the MATCH() function so I could
    >>> get relevance scoring.
    >>>
    >>> So I did
    >>> ALTER table Item ADD FULLTEXT (id, factory, type, category, section)
    >>>
    >>> however when I do a search with the MATCH query 'fo' doesn't match,
    >>> although 'foo' will.
    >>>
    >>> Is there a way to combine the MATCH query with the LIKE so that 'fo'
    >>> would match with the 'foo' entry and then a relevance score would be
    >>> generated?
    >>>
    >
    > You can change the minimum length of words supported by full-text indexes
    > (default : 4)
    > [url]http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html[/url] You can
    > also use AGAINST("fo*" IN BOOLEAN MODE)
    >
    >>> Thanks
    >>
    >>
    >> Fulltext indexing is rarely the best idea IMO, and the relevance ordering
    >> can usually be replaced with a good random number generator.
    >>
    >> I use LIKE and then write my own algorithm for sorting in my language of
    >> choice if relevance is important.
    >>
    >>
    >
    > That looks like a horrible strategy.
    > If you have just a few thousand records, you can cope with LIKE, and
    > client side processing, but when your data becomes huge, FULLTEXT is the
    > way to go. Searching with FULLTEXT can be up to twenty times faster than
    > LIKE (and usually more precise).

    How do you match ((C++ OR C# OR A+ OR .NET) AND "A-Z of Something") with a
    fulltext index?

    Handcrafted relevance algorithms, tuned to the nature of your data, are far
    superior and more precise that fulltext relevancy.



    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  6. #6

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    Every approach obviously has its own pros and cons. I wouldn't use
    'LIKE' and implement my own searching and sorting algorithm though,
    because that's typically just not scalable. As your database grows,
    certain searches may cause huge resultsets to be sent over the query
    port, which you'll then have to sort yourself and then throw out all
    but 10 of them (or whatever page size you're using)
    If you restrict the resultsets to a certain number of rows you're going
    to get more or less random results, as your database engine has no
    notion of relevance with 'LIKE' queries and does not return the
    possibly most relevant results.

    On the other hand, if you leave it up to MySQL you probably end up with
    some fairly limited set of search features, not including things like
    special character searches.

    I'd look into apache Lucene, but which is probably great for
    scalability but perhaps not trivial to set up.

    koala Guest

  7. #7

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    koala wrote:
    > Every approach obviously has its own pros and cons. I wouldn't use
    > 'LIKE' and implement my own searching and sorting algorithm though,
    > because that's typically just not scalable. As your database grows,
    > certain searches may cause huge resultsets to be sent over the query
    > port, which you'll then have to sort yourself and then throw out all
    > but 10 of them (or whatever page size you're using)

    You can limit this and ask the user to be more specific. Say you don't
    wish to sort more than 5000 records for scalability reasons, you use
    LIKE with LIMIT 5001.

    If it returns 5001 rows then ask the user to be more specific, if it
    returns less than 5001 rows then you've not missed anything.

    > If you restrict the resultsets to a certain number of rows you're going
    > to get more or less random results, as your database engine has no
    > notion of relevance with 'LIKE' queries and does not return the
    > possibly most relevant results.

    Fulltext indexing has no notion of relevance really. You can always
    tell which sites are using it as the percentage figures given bare no
    resemblance to what a human would give it, or a some well crafted code
    tailored to the data.

    Having experimented a lot with fulltext indexes and data specific
    algorithms, I am happy to say that in my experience, fulltext indexing
    is inferior. But, as always, TIMTOWTDI.



    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  8. #8

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    >If it returns 5001 rows then ask the user to be more specific, if it
    >returns less than 5001 rows then you've not missed anything.
    ok, that would be an improvement, but you'd still be pulling in up to
    5000 results over the query port, and then use your own criteria. I too
    have used this exact same approach on public websites, but am all too
    familiar with the performance problems of this functionality.

    Unfortunately, the database I was working with (not a sql database at
    all) didn't support the sorting algorithms that we needed to use so we
    had no other option. But this approach is still a last resort to me.
    Try measuring the performance difference between a query that returns
    10 results and a query that returns 5000 results. All I can say is I
    hope you have a small database, low site traffic or that your users are
    good searchers.

    koala Guest

  9. #9

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    koala wrote:
    >>If it returns 5001 rows then ask the user to be more specific, if it
    >>returns less than 5001 rows then you've not missed anything.
    >
    > ok, that would be an improvement, but you'd still be pulling in up to
    > 5000 results over the query port, and then use your own criteria. I too
    > have used this exact same approach on public websites, but am all too
    > familiar with the performance problems of this functionality.
    >
    > Unfortunately, the database I was working with (not a sql database at
    > all) didn't support the sorting algorithms that we needed to use so we
    > had no other option. But this approach is still a last resort to me.
    > Try measuring the performance difference between a query that returns
    > 10 results and a query that returns 5000 results.
    > All I can say is I
    > hope you have a small database, low site traffic or that your users are
    > good searchers.

    None of those :-)

    Paying extra cash for better hardware to allow us give far superior results
    is the route we have gone down, and it has paid off.


    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  10. #10

    Default Re: designing keyword searches, LIKE, MATCH AGAINST, etc...

    try using Match() in boolen mode with *
    e.g

    MATCH(column1,column2) AGAINST(\''.search_term.'*\' IN BOOLEAN MODE)


    Quote Originally Posted by ron1972 View Post
    Hi I have a table "Item" with 5 varchar columns

    id, factory, type, category, section

    i currently am using LIKE queries to generate keyword search results

    SELECT id from Item WHERE id LIKE ('%foo bar%') OR type LIKE ('%foo
    bar%') ... etc.

    However it was suggested to me to use the MATCH() function so I could
    get relevance scoring.

    So I did
    ALTER table Item ADD FULLTEXT (id, factory, type, category, section)

    however when I do a search with the MATCH query 'fo' doesn't match,
    although 'foo' will.

    Is there a way to combine the MATCH query with the LIKE so that 'fo'
    would match with the 'foo' entry and then a relevance score would be
    generated?

    Thanks
    Unregistered Guest

Similar Threads

  1. Enable Verity Searches???
    By MSK in forum Coldfusion Server Administration
    Replies: 5
    Last Post: May 17th, 04:14 PM
  2. Boolean Searches
    By freezer9 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 18th, 01:00 AM
  3. doing searches - need help really quickly
    By GilesW in forum Macromedia Flash Sitedesign
    Replies: 8
    Last Post: January 13th, 12:46 PM
  4. Keyword Searches
    By Daniel Park in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 14th, 12:30 PM
  5. Help with speeding up searches
    By T.Taylor in forum ASP
    Replies: 0
    Last Post: March 17th, 01:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139