SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast mjlivelyjr's Avatar
    Join Date
    Dec 2003
    Location
    Post Falls, ID, US
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [Full Text Search] Passing column to AGAINST()

    Is there a way in any version of MySQL to pass a column name to AGAINST when doing a full text search.

    I know the manual says no, but then this article says yes, sorta. Though I believe it may only be applicable to MySQL 5.x
    http://dev.mysql.com/tech-resources/...-revealed.html

    An example of the query that I would like to use is somewhat similar to this (shortened to save time):

    SELECT * FROM resumes, alerts
    WHERE resumes.resume_id = 1 AND alerts.type = 'resume'
    AND MATCH (resumes.content) AGAINST (alerts.keywords)

    The purpose of this is to implement an alert system. The site has basic search functionality for resumes, however if a search doesn't yield resumes a user also has the option to save the search and be automatically alerted when a new resume is added to the DB that matches the search. The way to do this that made most sense to me is to just check all the saved searches (alerts) after a resume is added. Part of the standard search however involves a full text search over the body of the resume, which is of course a piece of cake when matching resumes to a given search, however when I work it the other way around (matching alerts to a given resume) it's not so easy as I don't have a static string to use in AGAINST(). So is there way around this and if not is there any other way to do this that doesn't checking each and every alert individually? (The system could easily get into the hundreds of thousands of alerts eventually.)
    Last edited by mjlivelyjr; Feb 22, 2005 at 17:08.
    Mike Lively
    Digital Sandwich - MMM MMM Good

  2. #2
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wonder if you could create a full text index on the alerts keywords column and then use a join with the resume contents column? Possibly using FORCE INDEX. Be interesting to see if that would work.

  3. #3
    SitePoint Enthusiast mjlivelyjr's Avatar
    Join Date
    Dec 2003
    Location
    Post Falls, ID, US
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should try that just to see what happens but my bet is that I will either get no results or get very low relevency in the results as the alerts keyword column will most likely only contain less than 10 words and the resume body is...well a resume body .

    It's got more of a chance of working then what I have tried so far though so I might as well give it a spin, I'll let you know what I find out.

    So far this seems to have stumped quite a few people, posted it in 5 forums and I only got 2 answers, one of which was basically telling me to do what I want to avoid (creating my own full text engine) and this one. So hopefully this'll work well enough .

    Thanks for the suggestion.
    Mike Lively
    Digital Sandwich - MMM MMM Good

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, i saw probably 4 of those 5 posts, but i'm pretty sure the answer is "you can't do that" so i didn't bother to reply

    perhaps you could fake it

    generate a query string that looks like this:
    Code:
    select resumes.* 
         , 23       as alert_id
         , 'curly'  as alert_user
      from resumes
     where resumes.resume_id = 1 
       and match (resumes.content) against ( 'curly alert' ) 
    union all
    select resumes.* 
         , 25       as alert_id
         , 'larry'  as alert_user
      from resumes
     where resumes.resume_id = 1 
       and match (resumes.content) against ( 'larry alert' ) 
    union all
    select resumes.* 
         , 29       as alert_id
         , 'moe'    as alert_user
      from resumes
     where resumes.resume_id = 1 
       and match (resumes.content) against ( 'moe alert' )
    this query would naturally need to be constrained, because i'm sure you will eventually reach a limit on the length of the string if you get "hundreds of thousands of alerts"

    you would generate the query string by retrieving the contents of the alerts table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My reasoning was that the join of 2 indexed columns actually uses the indexes, each of which is a list of unique values and the record numbers that they occur in. With a full text index this is also the case, but with the addition of wieghting etc for the relevance calculation. Now if a join is possible, each word in the alerts index would be joined to the same word in the contents index. I only wonder if the db engine will allow such a thing? I'd try it myself if I had a mysql db that I could use to test it on - the only one I have is on our hosted website and I'm not about to play with that.

  6. #6
    SitePoint Enthusiast mjlivelyjr's Avatar
    Join Date
    Dec 2003
    Location
    Post Falls, ID, US
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh common, be brave (just kidding of course)

    Well, I'll be solving (hopefully) the problem today. I will try your method first and if that doesn't work I'll probably just go the route of adding a table to handle my own indexing of the resume contents. I should see how much time it really does take to issue queries accross all of the alerts. I have had two people now that don't seem to think it will take too long and they probably both know more about Databases then I do.

    In either case thanks to the both of you for the suggestions. I'll let you know what I come up with in the event that you are remotely interested.
    Mike Lively
    Digital Sandwich - MMM MMM Good

  7. #7
    SitePoint Enthusiast mjlivelyjr's Avatar
    Join Date
    Dec 2003
    Location
    Post Falls, ID, US
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I've played around with it for a couple of hours, forcing an index doesn't really work, it'll use the index like I ask it to (as per EXPLAIN) but there is still no way to match the values.

    I really didn't want to rewrite any sort of fulltext functionality and I didn't particularly want to build large queries so I think I'll just figure out how to deal with low relevancy results. That would probably be the easiest way to address the problem anyhow. Thanks again for the suggestion.
    Mike Lively
    Digital Sandwich - MMM MMM Good


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
  •