panduola — 2011-12-20T13:38:45-05:00 — #1
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...
r937 — 2011-12-20T13:51:47-05:00 — #2
sorry, i don't recognize the mysql error message "playing friendly"
perhaps you could elaborate on the results you are (or aren't) getting?
panduola — 2011-12-20T13:58:19-05:00 — #3
If I search for #testinghashtag, it returns all results matching "testinghashtag", as if the pound symbol were completely ignored.
Sorry for the ambiguity. Thanks!
r937 — 2011-12-20T14:07:26-05:00 — #4
i'm no fulltext expert, but try this
AGAINST ( '"#testinghashtag"' )
panduola — 2011-12-20T14:51:34-05:00 — #5
Thanks for the suggestion. I just tried it and the results are still the same. Any ideas?
r937 — 2011-12-21T01:58:51-05:00 — #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
panduola — 2011-12-21T12:26:54-05:00 — #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."
r937 — 2011-12-21T12:46:01-05:00 — #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?
panduola — 2011-12-21T12:56:35-05:00 — #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.