Search code examples
mysqlsearchfull-text-searchtransformsql-like

MySQL: Transform "LIKE" search to fulltext?

I have a pretty simple LIKE search for MySQL that i'd like to transform into a fulltext. The problem is i need to be able to implement it so that it starts with X. Like the example below:

SELECT column FROM table WHERE column LIKE "startswith%"

as you can see that query returns all results that begins with "startswith". I need to do this with a fulltext.

Is this possible to do?

Solution

  • No, that isn't how fulltext works (it's actually just a list with loose words underneath, no information about location relative to the string) but there's no reason why you can't have that LIKE ... as an extra WHERE clause. FULLTEXT can still help to get a smaller subset of results if you haven't got another key on column. If you do have a key on column, using FULLTEXT for this is useless.

    You can set a key on just the start of a column with ADD INDEX (column(123)); (which would only index the first 123 characters). THis also works for text/blob columns (in the latter case it's the binary length you give).