I'm developing using the Interspire shopping cart at the moment, and the DB is being used to store motor parts. Because of this, the records contain lots of values such as '2' and 'da'. When I use the Search function to search for 'da', I get the result I'm looking for, and when I search for 'b3', it's also returned. But when I search for 'da b3', this result can't be found.
By contrast, I made a dummy product with 'horse' and 'saddle' as keywords, and 'horse saddle' returns the right result.
So my question is: is this a MySQL feature, or a problem with the search function? The query produced by the search function is really long. I'll post it at the bottom anyway, but I'm not expecting anybody to trawl through it.
Any ideas?
Code: Select all
(SELECT SQL_CALC_FOUND_ROWS 'page' AS nodetype, p.pageid AS nodeid, p.pagetitle AS nodetitle, p.pagecontent AS nodecontent, p.pagelink AS nodelink, p.pagetype AS nodepagetype, p.pagevendorid AS nodevendorid, v.vendorfriendlyname AS nodevendorfriendlyname, (IF(p.pagetitle='da b3', 10000, 0) + ((MATCH (ps.pagetitle) AGAINST ('da b3')) * 10) + MATCH (ps.pagetitle,ps.pagecontent,ps.pagedesc,ps.pagesearchkeywords) AGAINST ('da b3')) AS score FROM [|PREFIX|]pages p INNER JOIN [|PREFIX|]page_search ps ON p.pageid = ps.pageid LEFT JOIN [|PREFIX|]vendors v ON p.pagevendorid = v.vendorid WHERE p.pagestatus = 1 AND (p.pagecustomersonly = 0 OR FALSE) AND (MATCH (ps.pagetitle,ps.pagecontent,ps.pagedesc,ps.pagesearchkeywords) AGAINST ('da b3' IN BOOLEAN MODE) OR p.pagetitle LIKE '%da b3%' OR p.pagesearchkeywords LIKE '%da b3%')) UNION (SELECT 'news' AS nodetype, n.newsid AS nodeid, n.newstitle AS nodetitle, n.newscontent AS nodecontent, NULL AS nodelink, NULL AS nodepagetype, NULL AS nodevendorid, NULL AS nodevendorfriendlyname, (IF(n.newstitle='da b3', 10000, 0) + ((MATCH (ns.newstitle) AGAINST ('da b3')) * 10) + MATCH (ns.newstitle,ns.newscontent,ns.newssearchkeywords) AGAINST ('da b3')) AS score FROM [|PREFIX|]news n INNER JOIN [|PREFIX|]news_search ns ON n.newsid = ns.newsid WHERE n.newsvisible = 1 AND (MATCH (ns.newstitle,ns.newscontent,ns.newssearchkeywords) AGAINST ('da b3' IN BOOLEAN MODE) OR n.newstitle LIKE '%da b3%' OR n.newssearchkeywords LIKE '%da b3%')) ORDER BY score DESC LIMIT 16