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…

sorry, i don’t recognize the mysql error message “playing friendly” :wink:

perhaps you could elaborate on the results you are (or aren’t) getting?

If I search for #testinghashtag, it returns all results matching “testinghashtag”, as if the pound symbol were completely ignored.

Sorry for the ambiguity. :slight_smile: Thanks!

i’m no fulltext expert, but try this

AGAINST ( '"#testinghashtag"' ) 

Thanks for the suggestion. I just tried it and the results are still the same. Any ideas?

could you run this please –

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
rows	tags	hashtags
304866	760	1

These stats are indeed correct. 760 instances of “testinghashtag” with only one of the real hash tag “#testinghashtag.”

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?

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.