Wild Card Before and After a String - MySql, PSQL

Question

I need to perform Contains operation in a column. For Contains operation we need use Wildcard before and after a word.

Ex: personalized

Query -> like '%sonal%'

As this type of query can't use indexes. Is there any way to increase the speed of the search.

Note: I use MySql(InnoDB) and PSQL

Answer 1

PostgreSQL has solution - trigram index. Here is a article or documentation

postgres=# create extension pg_trgm ;
CREATE EXTENSION
postgres=# create index on obce using gin (nazev gin_trgm_ops);
CREATE INDEX
postgres=# explain select * from obce where nazev like '%Bene%';
┌──────────────────────────────────────────────────────────────────────────────┐
│                                  QUERY PLAN                                             │
╞══════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=20.00..24.02 rows=1 width=41)                           │
│   Recheck Cond: ((nazev)::text ~~ '%Bene%'::text)                                       │
│   ->  Bitmap Index Scan on obce_nazev_idx  (cost=0.00..20.00 rows=1 width=0)            │
│         Index Cond: ((nazev)::text ~~ '%Bene%'::text)                                   │
└──────────────────────────────────────────────────────────────────────────────┘
(4 rows)

It is working for regular expressions too.

Answer 2

MySQL supports FULLTEXT indexes.

You might be interested in my presentation Full Text Search Throwdown, in which I compare different fulltext indexing tools. The presentation is a bit old now, but some of it is still relevant.

---

Re your comments:

MySQL's fulltext indexing doesn't support partial word matches, although it supports a limited wildcard, but only at the end of patterns. And the InnoDB implementation of fulltext doesn't support it, only the MyISAM does. See mention of the * wildcard in https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

SELECT ... WHERE MATCH(mycolumn) AGAINST ('stack*' IN BOOLEAN MODE)

Elastic Search also support wildcards, but like MySQL, they aren't efficient if your wildcard is at the start of the pattern. See https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-wildcard-query.html

Sphinx Search supports an option for infix string indexing. If you set min_infix_len to a nonzero positive number, it will index all infix substrings as well as whole words. See http://sphinxsearch.com/docs/current.html#conf-min-infix-len