SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking help needed tweaking fulltext search optimization query

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

    Code SQL:
       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
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would remove the one on both columns, and change the WHERE clause to this --
    Code:
    WHERE MATCH(title) AGAINST('+dg43nb*' IN BOOLEAN MODE)
       OR MATCH(description) AGAINST('+dg43nb*' IN BOOLEAN MODE)
       OR product_code = 'dg43nb'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •