mysql like search

Question

MySQL

The task is relatively simple, but a loaded project.

There is a field in the table varchar(255). The table has many fields and about 300,000 rows, but will grow to about 1,000,000.
Now the search is done like this name LIKE '%$name%' OR name '%$translit%'

The search slows down.

In this regard, there is an option
to put the id, name fields completely into a separate search table and use the same LIKE '%$name%"'

Will it speed up the search?

Well, the second option is to fool around with FULLTEXT, but I never used it, is there meaning? Judging by the docs, it bypasses fewer lines.

Morphology is not important.

The third option I saw in the forums. Split into words and make a word index table and search through it.

Which way to dig?

Answer 1

1) Taking out id and search in a separate table will not help.
2) Put the sphinx and forget about the brakes in the search.
habrahabr.ru/blogs/sphinx/

Answer 2

FULLTEXT should reduce the load, but if you use InnoDB tables, you will have to duplicate the search index into a MyISAM table. If it does not help and access to the server is limited, then try Zend Lucene

Answer 3

The third option will reduce the load, I think even better than full text, and I think this is the best option.
Using the third option, you will not need to use text engines, just a banal string comparison, this is a pretty fast operation, you should be able to handle a million with a bang, you can score a couple of million random lines for the test, and try.