SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Fulltext Search: Pound Signs

    Hi there,

    I'm trying to do a fulltext search on a column post with the following type of search term:

    WHERE MATCH (post) AGAINST ("#testinghashtag")

    and MySQL doesn't seem to be playing friendly with the POUND SYMBOL. What's the workaround for this? Do I need to store # as a different character in the post column itself?

    Thanks in advanced...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't recognize the mysql error message "playing friendly"

    perhaps you could elaborate on the results you are (or aren't) getting?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I search for #testinghashtag, it returns all results matching "testinghashtag", as if the pound symbol were completely ignored.

    Sorry for the ambiguity. Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm no fulltext expert, but try this
    Code:
    AGAINST ( '"#testinghashtag"' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion. I just tried it and the results are still the same. Any ideas?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you run this please --
    Code:
    SELECT COUNT(*) as rows
         , COUNT(CASE WHEN post LIKE '%testinghashtag%' THEN 'ok' END) AS tags
         , COUNT(CASE WHEN post LIKE '%#testinghashtag%' THEN 'ok' END) AS hashtags
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    rows	tags	hashtags
    304866	760	1
    These stats are indeed correct. 760 instances of "testinghashtag" with only one of the real hash tag "#testinghashtag."

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, there goes the theory i had, that there weren't enough rows to return accurate results

    i guess i'm stumped (wouldn't be the first time, eh)

    what about sticking with LIKE, is that feasible? how was the performance on that count query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    330 ms. Would you consider that to be good performance? But the question is: what will that look like when there are 1,000,000 posts? 5,000,000? The forum is rapidly expanding, so I foresee a potential scalability problem years out.

    Thoughts? Thanks again.


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
  •