I’m having trouble with a FullText search in MySQL. I made a FullText index on the item_desc column in my table. The table has three rows with ‘Sodium Chloride’ in them:
Solution Sodium Chloride standard
5425 Sodium Chloride 100u
QtySodium Chloride solution
I’m using this query:
SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST('"*Sodium Chloride*"' IN BOOLEAN MODE);
It only finds the first two rows. It misses the third one where ‘Sodium’ is stuck to another word.
A LIKE query works fine:
SELECT * FROM tblhugedata WHERE Item_desc LIKE '%SODIUM CHLORIDE%';
But I need to use the FullText index. Is there a way to make MATCH AGAINST work like LIKE for this case? I want it to find all three rows.
yo jessicadream12, fulltext can be a pain! have u tried this? SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST(‘sodium* chloride*’ IN BOOLEAN MODE); the asterisks should catch partial matches. might solve ur problem with stuck words. good luck!
I’ve encountered this issue before when working with FullText search in MySQL. One approach that might help is to use the CONCAT function along with MATCH AGAINST. Try this query:
SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST(CONCAT(‘"’, REPLACE(‘Sodium Chloride’, ’ ', ‘" "’), ‘"’) IN BOOLEAN MODE);
This dynamically creates a search string with quotes around each word, which can help catch cases where words are stuck together. It’s not perfect, but it often improves results without sacrificing the performance benefits of FullText search.
If this doesn’t work, you might need to consider adjusting your MySQL’s ft_min_word_len setting or exploring alternative full-text search engines for more precise matching capabilities.
I’ve encountered similar issues with MySQL FullText search. The problem lies in how FullText tokenizes words. For your specific case, try this query:
SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST(‘sodium chloride’ IN BOOLEAN MODE);
This should match all three rows, including the one where ‘Sodium’ is concatenated. If that doesn’t work, you might need to adjust your MySQL’s minimum word length setting or consider using a more advanced full-text search engine like Elasticsearch for better phrase matching capabilities.
Remember, FullText search prioritizes relevance over exact matching, which can sometimes lead to unexpected results compared to LIKE queries.
I’ve dealt with similar FullText search quirks in MySQL before. One workaround that’s worked for me is using the REGEXP operator combined with the FullText index. Try this query:
SELECT * FROM tblhugedata WHERE MATCH(Item_desc) AGAINST(‘sodium chloride’ IN BOOLEAN MODE) AND Item_desc REGEXP ‘[[:<:]]sodium[[:>:]].*[[:<:]]chloride[[:>:]]’;
This approach leverages the speed of FullText indexing while ensuring exact phrase matching. The REGEXP part checks for whole words ‘sodium’ and ‘chloride’ in that order, catching cases where they’re stuck to other words.
Keep in mind this might be slower than a pure FullText search, but it’s still faster than a full table scan with LIKE. If performance becomes an issue, you might want to look into more robust full-text search solutions outside of MySQL.