SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    USA
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Fulltext searching - adjusting relevance?

    !
    Last edited by DaveB2; Jan 14, 2007 at 17:58.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select title
         , description
         , match('title keywords') against (title)
         , match('description keywords') against (description) * 0.5
      from thetable
     where match('title keywords') against (title)
        or match('description keywords') against (description)
    order
        by match('title keywords') against (title)
         + match('description keywords') against (description) * 0.5
    this weights the description at half the weight of the title.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    USA
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    !
    Last edited by DaveB2; Jan 14, 2007 at 17:58.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    USA
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    !
    Last edited by DaveB2; Jan 14, 2007 at 17:59.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, you need two indexes. it will be slower because mysql has to search two indexes, but you probably won't notice the impact.

    no, you can't skip the markup. what you can do is insert the text into two different colums, and use PHP to strip the markup in the one column. in that case, only index the stripped column.

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    USA
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    !
    Last edited by DaveB2; Jan 14, 2007 at 17:59.

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    USA
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    !
    Last edited by DaveB2; Jan 14, 2007 at 17:59.

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    good question. i'm not sure. but just to be safe, you could replace all of your markup with a single space instead of just stripping it.

  9. #9
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks to your method, I've been able to present more relevant results at the top when the keywords were present in the title field.

    I'm using similar fields when searching, which is title and description.
    To make the results better:

    1) I've considered increasing the relevance for articles residing in particular categories (designated by ids in a tinyid column field).

    2) Increasing relevance for articles with higher viewing rates (i.e.popularity). The field in use is int() count.

    How can i do that in the mysql query statement? I considered using MATCH() AGAINST() for the category and count fields but since they are integers, its not possible. Any ideas please?

    Thanks
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    see Simple Keyword Relevance for an alternate approach
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    see Simple Keyword Relevance for an alternate approach
    Yeah. I read that article, but thats about keyword relevance. I'm trying to expand the relevance to include data from other columns.

    I.e.
    Assuming result row is any row that contains any of the search parameters (based on keywords in title/description)

    Increase relevance of particular result row if article hits > 10000 by 1.5
    Increase relevance of particular result row if article hits > 100000 by 2
    Increase relevance of particular result row if article belongs to category A by 1.5
    Increase relevance of particular result row if article belongs to category B by 2

    I'm thinking, if an IF statement can be added to the select statement to achieve that result. Does MySQL support IF statements for this purpose?
    Can anyone shed some light on this? Thanks
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    one thing i hoped that the article would suggest is that the method can easily be extended to any kind of relevance:
    Code:
    select keyword relevance 
           + case ( select count(*) from hits ) 
               when > 100000 then 2
               when > 10000  then 1.5
                             else 0 end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937, That helped alot.
    What if I want to:

    Add 2 when hits > 100000 AND hits <= 400000
    Add 4 when hits > 400000

    CASE allows only 1 action/respond. Is there a possible IF alternative that allows me to deal with multiple responds based on multiple queries?

    Thanks alot.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lynlimz
    CASE allows only 1 action/respond.
    no, it allows multiples:
    Code:
    case when hits > 100000 AND hits <= 400000
         then 4
         when hits > 400000 
         then 2
         else 0 end
    however, i wouldn't do it like that

    the above is exactly the same as this --
    Code:
    case when hits > 400000 
         then 2
         when hits > 100000 
         then 4
         else 0 end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •