Professional Web Applications Themes

MySQL search with result scoring / relevance - MySQL

Hi, Suppose i have the following sql table: my_table (col_1, col_2, col_3, col_4) and the folowing user input for the search: col_1:value_1 col_2:value_2 value_3 The above input means the folowing: search col_1 for value_1, col_2 for value_2 and value_3 in all columns. Besides this i need some scoring (this is used for sorting the results by their relevance). A match in col_1 will be 4 points, col_2 will be 3 points, col_3 will be 2 poits and the last one 1 point. All the results will be sorted by computing this score. For example: matching in col_1 for value_1 (+4), ...

  1. #1

    Default MySQL search with result scoring / relevance

    Hi,

    Suppose i have the following sql table:
    my_table (col_1, col_2, col_3, col_4)

    and the folowing user input for the search:
    col_1:value_1
    col_2:value_2
    value_3

    The above input means the folowing: search col_1 for value_1, col_2
    for value_2 and value_3 in all columns.

    Besides this i need some scoring (this is used for sorting the results
    by their relevance).
    A match in col_1 will be 4 points, col_2 will be 3 points, col_3 will
    be 2 poits and the last one 1 point.

    All the results will be sorted by computing this score.
    For example: matching in col_1 for value_1 (+4), matching in col_2
    (+3) and col_4 (+1) for value_3 will mean a +8 score for a row.

    I need some ideas and if it is possible some web links that might help
    me or even code.
    I'm also open to any suggestions that can offer a solution to my
    problem.

    Regards,
    Marius.

    mareeus@gmail.com Guest

  2. #2

    Default Re: MySQL search with result scoring / relevance

    Marius,

    I think something like this is what you want:

    DROP TABLE IF EXISTS `test`.`test`;
    CREATE TABLE `test`.`test` (
    `TestID` int(11) NOT NULL auto_increment,
    `Col1` varchar(255) default NULL,
    `Col2` varchar(255) default NULL,
    `Col3` varchar(255) default NULL,
    `Col4` varchar(255) default NULL,
    PRIMARY KEY (`TestID`)
    ) ENGINE=InnoDB ;

    insert Test (col1, col2, col3, col4) select 'mystring', '', '', '';
    insert Test (col1, col2, col3, col4) select '', 'mystring', '', '';
    insert Test (col1, col2, col3, col4) select '', '', 'mystring', '';
    insert Test (col1, col2, col3, col4) select '', '', '', 'mystring';
    insert Test (col1, col2, col3, col4) select 'mystring', 'mystring',
    'mystring', 'mystring';

    select
    Col1
    , Col2
    , Col3
    , Col4
    , case when ifnull(Col1,'') = 'mystring' then 4 else 0 end /* now lets
    add them all up */
    + case when ifnull(Col2,'') = 'mystring' then 3 else 0 end
    + case when ifnull(Col3,'') = 'mystring' then 2 else 0 end
    + case when ifnull(Col4,'') = 'mystring' then 1 else 0 end
    MyRank
    from Test

    -- Bill


    <com> wrote in message
    news:googlegroups.com... 


    AlterEgo Guest

  3. #3

    Default Re: MySQL search with result scoring / relevance

    Thanks a lot Bill.

    Your example works very fine.

    select
    Col1
    , Col2
    , Col3
    , Col4
    , case when ifnull(Col1,'') = 'mystring' then 4 else 0 end /* now
    lets
    add them all up */
    + case when ifnull(Col2,'') = 'mystring' then 3 else 0 end
    + case when ifnull(Col3,'') = 'mystring' then 2 else 0 end
    + case when ifnull(Col4,'') = 'mystring' then 1 else 0 end
    MyRank
    from Test


    Now i have another ouestion. Suppose the folowing user input:
    col_1:value_1 or (col_2:value_2 and col_3:value_3) or col_4:value_4

    I need to adapt your example to work with "()", "AND", "OR", and in
    the case of one condition not meeting the boolean expression to
    exclude that row from my selection.

    mareeus@gmail.com Guest

  4. #4

    Default Re: MySQL search with result scoring / relevance

    I've solved the 'or' and 'and' problem but now I'm facing a new one (a
    shameful syntax error).
    This is my query:

    select *
    , case when `col1` LIKE '%val1%' then 1 else 0 end Token1
    , case when `col2` LIKE '%val2%' then 2 else 0 end Token2
    , case when Token1 * Token2 > 0 then Token1 * Token2 else 0 end score
    from `table` HAVING score > 0 order by score DESC

    I get the following error:
    #1054 - Unknown column 'Token0' in 'field list'

    How can i make this work keeping Token1 and Token2 and using them to
    compute the score and order results by score

    mareeus@gmail.com Guest

  5. #5

    Default Re: MySQL search with result scoring / relevance

    On Mar 28, 8:22 am, "com" <com> wrote: 

    My last post attempt seemed to fail...lets try again...

    You cant reference aliases from within the field list. You have to re-
    state the formulas again.

    Heres my possible solution to it all...

    SELECT
    col1,
    col2,
    col3,
    col4,
    ((`col1` LIKE '%val1%') * 4) +
    ((`col2` LIKE '%val2%') * 3) +
    ((`col3` LIKE '%val3%') * 2) +
    ((`col4` LIKE '%val4%') * 1) score,
    (`col1` LIKE '%val1%') OR
    ((`col2` LIKE '%val2%') AND
    (`col3` LIKE '%val3%') ) OR
    (`col4` LIKE '%val4%') booleanMatch
    FROM test
    HAVING booleanMatch = 1 AND score > 0 order by score DESC

    Tigger Guest

  6. #6

    Default Re: MySQL search with result scoring / relevance

    Thanks Tigger,

    Your solution is almost perfect but imagine a complex search string
    with 10 or 20 search values. Computing both score and booleanmatch
    won't be very optimized. They both mean almost the same thing. That's
    why i need some temporary values like Token1 and Token2 to store the
    values once computed and reduce the query complexity a little. So if
    anyone could help me to rewrite my query keeping Token1 and Token2,
    would be great.

    select *
    , case when `col1` LIKE '%val1%' then 1 else 0 end Token1
    , case when `col2` LIKE '%val2%' then 2 else 0 end Token2
    , case when Token1 * Token2 > 0 then Token1 * Token2 else 0 end score
    from `table` HAVING score > 0 order by score DESC

    #1054 - Unknown column 'Token0' in 'field list'

    Regards,
    Marius.

    mareeus@gmail.com Guest

  7. #7

    Default Re: MySQL search with result scoring / relevance

    On Mar 28, 11:44 pm, "com" <com> wrote: 


    You could play around with the order of things. Like

    SELECT
    col1,
    col2,
    col3,
    col4
    FROM test
    WHERE
    (`col1` LIKE '%val1%') OR
    ((`col2` LIKE '%val2%') AND
    (`col3` LIKE '%val3%') ) OR
    (`col4` LIKE '%val4%')
    ORDER BY (((`col1` LIKE '%val1%') * 4) +
    ((`col2` LIKE '%val2%') * 3) +
    ((`col3` LIKE '%val3%') * 2) +
    ((`col4` LIKE '%val4%') * 1)) DESC

    This would limit the 2nd calculations to only happen for rows that
    match the boolean expression.

    Another thing to look at is doing Full-Text Searches using the MATCH
    AGAINST syntax...
    http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

    This can provide cleverer results for your searches, and I remember
    reading once that repeating the same MATCH statement in the field and
    in a where clause will only cause the one calculation, so you don't
    have to worry about the same efficiency issue.

    Tigger Guest

Similar Threads

  1. problems paging a recordset search result
    By CharitiesOnline in forum ASP Database
    Replies: 1
    Last Post: July 3rd, 10:21 AM
  2. Replies: 6
    Last Post: October 9th, 04:53 PM
  3. search result case problem
    By Ben in forum PERL Miscellaneous
    Replies: 4
    Last Post: October 15th, 11:59 AM
  4. search result
    By Phpu in forum PHP Development
    Replies: 2
    Last Post: September 21st, 10:42 PM
  5. scoring/sorting db search results based on score
    By Dave in forum PHP Development
    Replies: 5
    Last Post: July 21st, 10:21 AM

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