What is a good indexing method for such a query?

Question

Jeff mentions at http://blog.stackoverflow.com/2011/03/redesigned-users-page/ that

• Repeat searches match again, but the minimum match is now 3 characters.

Great stuff! This has been “broken” for quite some time due to the performance of the LIKE match, and the fact that the full-text search is inaccurate (for example, the initials in the username would be too short to use in any full-text index).

There must be a way to do this, and I have an idea, but I would like to see if anyone has a solution (alternative) for this task.

Request example:

SELECT TOP 36 * FROM users WHERE nickname LIKE '%' + @search + '%' 

Note: although the tag is an sql server due to a maximum of 5 tags, I am more than happy to research solutions in other DBMSs for portability.

Answer 1

Postgres at least recently added a solution to map trigrams to its standard modules. He rewrites the LIKE query into several trigram-related queries. Unfortunately, the size of the index is usually huge.

There is also Wildspeed , which also suffers from huge indexes. I assume the size depends on the length of the column.