How to speed up LIKE operation in SQL (Postgres preferably)

We have a Postgres database which contains 2 millions entries. We have tried using equality search and it works instantly (SELECT * FROM a WHERE b = "asd")

But we would like "LIKE '%asd%'" o开发者_运维技巧peration to be fast too. How do we do that?

Generally like '%something%' is not indexable.

But.

There are couple of issues:

1. Are you absolutely sure that you need full substring match? Perhaps you could do with "any word (whitespace separated) starts with something"? - it's close to %something% and definitely indexable.
2. You might want to use full text searches, but they usually work on words, so it's even less applicable to your solution if you positively really need substring search.
3. You can try to use trigrams for your search (module pg_trgm in contrib)
4. You can also try wildspeed, but be warned that it makes huge indexes.

You need full text index. This may help http://wiki.postgresql.org/wiki/Full_Text_Indexing_with_PostgreSQL

You won't be able to optimize this as it stands.

Because of the wildcard at the front of the search, it is required to scan the entire table for matches, meaning it can't use indexes.

You can't really speed it up because that syntax will not allow the indexes to be used. If at all possible you should never use a wildcard as the first part of a LIKE. Without knowing the first character of the field, there is no way possible to use the index, hence you get a table scan which is slow.

Personally I never let my users do a search without giving me the begining of what they are searching for. In SQL server if you must do this, you can set up a full-text search but I don't know if Postgres has that.

Use some kind of "full text" search index, for example PostGres looks like it has some support built in here.