help needed tweaking fulltext search optimization query

[azuranz]

Heya

I need help with my product search query.


Overall I'd appreciate any critique / tweak suggestions,
more importantly though I have an issue..

The reason I implemented this was to stop people
searching for "ipod" and getting results like "tripod"

but now my issue is if I search for "dg43nb" I get 0 results
(but I want to get "BOXd43nb")

So I need some guidance as to what my options are here.
Below is my query as it stands..

   SELECT SQL_CALC_FOUND_ROWS *, 
          IF((`category`IN ('dg43nb')),3,1)+ 
          MATCH(title) AGAINST('+dg43nb*' IN BOOLEAN MODE)*3+ 
          MATCH(description) AGAINST('+dg43nb*' IN BOOLEAN MODE)*0.2
          AS score 
     FROM inventory i 
LEFT JOIN category c 
       ON i.cat_id = c.cat_id
    WHERE MATCH(title,description)
          AGAINST('+dg43nb*' IN BOOLEAN MODE) 
       OR product_code = 'dg43nb'
 ORDER BY score DESC

any advice appreciated.
who knows if I'm lucky maybe r937 will swing by lol

[r937]

to stop searches for 'ipod' returning 'tripod', you may alternatively use the "whole word" delimiters in REGEXP

you appear to have three separate fulltext indexes on two columns, where only two would do

i'm not nuts about fulltext searching, presumably you could use LIKE on a title column but description might be too big???

[azuranz]

wasnt sure how many indexes there are so i checked...

and yeah theres a fulltext index on: title,description, title&description
Which would I remove and would I need to modify the query?

[r937]

i would remove the one on both columns, and change the WHERE clause to this --

WHERE MATCH(title) AGAINST('+dg43nb*' IN BOOLEAN MODE)
   OR MATCH(description) AGAINST('+dg43nb*' IN BOOLEAN MODE)
   OR product_code = 'dg43nb'

[azuranz]

Did that thanks.

now if only there was a semi-reliable way to return
boxdg43nb for search:dg43nb ,
but not tripod for search:ipod lol

the only way to do it would be to go start adding specific keywords to exclude isn't it..
like it's beyond mysql basic ability right