Clever Geek Handbook

How to speed up LIKE in SQL (preferably Postgres)

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

But we would like the "LIKE"% asd% "operation to be fast, how do we do that?

+2


source to share


5 answers


You need a complete text index. This might help http://wiki.postgresql.org/wiki/Full_Text_Indexing_with_PostgreSQL



+2


source


Generally, "% something%" is not indexed.

But.



There are several problems:

  • Are you absolutely sure you want a complete substring? Perhaps you can do "any word (space-separated) starts with something"? - it is close to%% and is definitely indexed.
  • You might want to use full text search, but they usually work on words, so it is even less applicable to your solution if you really need a substring search.
  • You can try using trigrams for search (pg_trgm module in contrib)
  • You can also try wildspeed , but be careful about making huge indices .
+3


source


You cannot speed it up because this syntax will not allow indexes. 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, it is not possible to use the index, so you end up with a slow table scan.

Personally, I never let my users search without giving me a start on what they are looking for. In SQL Server, if you have to do this, you can set up full text search, but I don't know if Postgres has that.

+2


source


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

Because of the pattern, at the start of the search, the entire table needs to be scanned for matches, which means it cannot use indexes.

0


source


Use some sort of full text search index, for example PostGres looks like it supports the built in here .

0


source






More articles:


All Articles