With SQL, I need to select rows which have a pattern like %hello% (i.e. <anything>hello<anything>) in a column, with potentially 100 millions of rows or more.
In the answer Infix search in MySQL with an index, it is said that:
- an index won't help
- even an FTS index won't help either
Question: are there RDBMS (e.g. PostgreSQL) that have features that can speed up queries like
select * from t where key like "%hello%"
and avoid a full table scan?
With specific data structures (tries, etc.), or bigrams, trigrams, etc.
TL;DR: I have a MySQL InnoDB like this:
create table t (id int primary key auto_increment,
key varchar(200), value varchar(200));
create index key_index on t(key) using BTREE;
create index value_index on t(value) using BTREE;
and I would like to do this without a full table scan:
select * from t where key like "%hello%"
Notes:
the strings are not words separated by spaces. Columns
keyandvaluehave typically 10 to 50 characters, with no spaces. They can beabcdefhelloworldblablabla.see also What is it called when you search the middle of a string instead of the beginning?
