MySQL Fulltext Search: Pound Signs

Databases
1

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

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?

3

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!

4

i’m no fulltext expert, but try this

AGAINST ( '"#testinghashtag"' )
5

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

6

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
7 
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

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?

9

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.