MySQL Forums
Forum List  »  MyISAM

Re: FullText Search - Substring match, possible?
Posted by: Rick James
Date: January 24, 2012 10:50AM

> I wanna show user WT13i even when 13i is typed and searched.
Cannot do with FULLTEXT. Workarounds (may or may not be practical):
* When ingesting the data, build a list of synonyms. That is, associate both WT13i and 13i with this product in a column that is indexed via FULLTEXT.
* If you get nothing from a FT search, then issue a second query using LIKE '%13i%' to see if it finds something. This may be unacceptably slow.

> mechanism of ordering the results
* See MATCH. Use it both for filtering and for providing a "relevance"

> +A +B should come before results which have A or in them, A and B being the searched strings.
* UNION two SELECTs. Include an extra column that is hardcoded with 1 for the +A +B SELECT, and 2 for the other select. Then use it in the ORDER BY:
    ( SELECT 1 as x, ... )
    ( SELECT 2 as x, ... )
    ORDER BY x, ...;
In some fashion, you may need to throw away `x` before displaying the results to the user. This can be done either by making the above a subquery to another SELECT, or in your application code.

> in big databases, which have >50000 rows
"big"? 45% of the tables mentioned in these forums have more than 50K rows.
FULLTEXT is clearly better than LIKE for 50K rows. You could also look into Sphinx and Lucene as alternatives to FULLTEXT; they may perform significantly better than FT. I do not know if they have tricks for any of the things you are looking for.

Options: ReplyQuote

Written By
Re: FullText Search - Substring match, possible?
January 24, 2012 10:50AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.