I have a query that works really good on searches in my table. However, I came across this certain search that resulted in no rows, even though I just added these rows and I know they are there (about 50 of them that should match).

Query as follows:

SELECT MATCH(SoundTitle, FilePathAndName, Description) AGAINST('(gamer) ("gamer")' IN BOOLEAN MODE) as score, 
    RowID, SoundTitle, FilePathAndName, Description, DatePublished
    FROM SoundEffects q1
    having score>0
    ORDER BY score DESC

Here's a sample of some rows returned using a like '%gamer%' query:

491     36      LittleGirlGamer/360 no scope_LittleGirlGamer.mp3        2019-01-17  0
492     36      LittleGirlGamer/Ask your gramma_LittleGirlGamer.mp3     2019-01-17  0
493     36      LittleGirlGamer/BRB_LittleGirlGamer.mp3     2019-01-17  0
494     36      LittleGirlGamer/Complaining_Aimbot_LittleGirlGamer.mp3      2019-01-17  0
495     36      LittleGirlGamer/Complaining_Hacker_LittleGirlGamer.mp3      2019-01-17  0

No, there are not 50% or more of rows in this table that would qualify for this search term. It would only be about 10% or so in the table.

The word gamer is not a stop word as far as I have read for InnoDB table.

Like I said, this query was working super great on other searches.

Thanks, Dave

  • Fulltext search given literal as a whole word or as a leading part of a word (while asterisk operator added). But in your case the literal you want to search for is non-leading substring. – Akina Jan 18 at 8:27
  • In other words, what can I do? I'm a bit confused. – Dave Fes Jan 18 at 20:08
  • what can I do? Use string/pattern matching instead of fulltext search, for example... or use external searching engines which can search by substring. – Akina Jan 18 at 20:37
  • It looks like if I separate the filenames with underscores or spaces in between words, the FT search picks those words up. In other words, I simply need to NOT combine words such as LittleGirlGamer. Instead, use Little_Girl_Gamer, or Little Girl Gamer. This will make FTS pick it up! – Dave Fes Jan 18 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.