zondag 9 februari 2014

Why I like PostgreSQL more than MySQL: LIKE '%hello' can use indexes.

LIKE searches for substrings and if the search expression starts with a wildcard a B-tree index cannot be used (it is sorted on characters that are most likely not even in the search expression).

In PostgreSQL however, you can create a Trigram index which effectively indexes every possible combination of substrings that the LIKE could chop the string into. So, if your field contains "Hello", then the index will contain "Hello", and "ello" and "llo" and so on (yes, a frightening amount of combinations is created and care should be taken if high insert performance is required)  When searching, the index will always have the chopped version of the string in the index and will return a result very quickly indeed.