Optimization of MySQL search using "like" and wildcards

Question

How can queries like

SELECT * FROM sometable WHERE somefield LIKE '%value%'

be optimized?

The main issue here is the first wildcard which prevents DBMS from using index.

Edit: What is more, somefield value is solid string (not a piece of text) so fulltext search could not be performed.

Answer 1

Use Full Text Search. The "Initial Idea" heading has the same example and leads to worked example solution.

And the MySQL docs

Edit: It can't be tuned in SQL itself. Using functions like LOCATE or PATINEX won't help either.

Answer 2

It won't make a huge difference, given your problem is with the wildcard, but not using "SELECT *" will improve query performance. If you're not actually using all the fields you get back, that's a win and "SELECT *" causes two queries to fire, one to look up the fields for the table and then your query with the field names added in.

Answer 3

Two ways:

(1) use an in-memory table so it goes very fast.

(2) cook up a better index and search algorithm than foo LIKE '%bar%'. It's not possible to make any suggestions about this without knowing more about your problem.

As you have pointed out, the %bar% pattern guarantees a table-scan for every lookup, which nullifies any possible search ingenuity in the database software.