CODEWITHSUNDEEP
mysqlsqlperformancepostgresqlstring-matching
Domenico
Domenico

Reputation: 79

sql substring performance

I have a table with 2 columns: 

id | word

The total rows are around 200 thousand. I want to find sub-string in the second column in the fastest way. I have tried with index btree and hash, but these methods increase the performance only if:

SELECT * FROM tbl WHERE word like 'string'.

But I want tune this query:

SELECT * FROM tbl WHERE word like '%sub-string%'

Here is the problem example: http://www.labdg.com/en/anagrammi.php?&lg=EN

Upvotes: 1

Views: 1013

Answers (1)

user330315
user330315

Reputation:

For PostgreSQL the condition like '%sub-string%' can be tuned by using a trigram index.

See this blog posts for details:

http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/
http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html

Using the full text search capabilities of both engines is also an option.

Upvotes: 4

Related Questions