> 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, ... )
UNION ALL
( 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.