Что лучший способ состоит в том, чтобы реализовать поиск подстроки в SQL?

Посмотрите мою презентацию Практический полнотекстовый поиск в MySQL.

Я сравнил:

• LIKE предикаты
• Regular expression предикаты (не лучше, чем LIKE)
• MyISAM FULLTEXT индексация
• Sphinx Search
• Apache Lucene
• Инвертированная индексация
• GoogleCustom Search Engine

Сегодня я бы использовал Apache Solr, который превращает Lucene в сервис с кучей дополнительных возможностей и инструментов.

По поводу вашего комментария: Ага, хорошо, нет. Ни одна из упомянутых мною возможностей полнотекстового поиска не поможет, поскольку все они предполагают наличие границ между словами

Другой способ эффективного поиска произвольных подстрок - это подход N-граммы. По сути, создайте индекс всех возможных последовательностей из N букв и укажите строки, в которых встречается каждая соответствующая последовательность. Обычно это делается при N=3, или триграмме, потому что это компромисс между поиском более длинных подстрок и сохранением управляемого размера индекса.

Я не знаю ни одной базы данных SQL, которая бы прозрачно поддерживала индексацию N-грамм, но вы можете настроить ее самостоятельно, используя инвертированный индекс:

create table trigrams (
  trigram char(3) primary key
);

create table trigram_matches (
  trigram char(3),
  document_id int,
  primary key (trigram, document_id),
  foreign key (trigram) references trigrams(trigram),
  foreign key (document_id) references mytable(document_id)
);

Теперь заполните ее сложным способом:

insert into trigram_matches
  select t.trigram, d.document_id
  from trigrams t join mytable d
    on d.textcolumn like concat('%', t.trigram, '%');

Конечно, это займет довольно много времени! Но как только это будет сделано, вы сможете искать гораздо быстрее:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'

Конечно, вы можете искать детали длиннее трех символов, но инвертированный индекс все равно помогает значительно сузить поиск:

select d.*
from mytable d join trigram_matches t
  on t.document_id = d.document_id
where t.trigram = 'abc'
  and d.textcolumn like '%abcdef%';

Если вы хотите найти целые слова, посмотрите на индекс FULLTEXT и MATCH() AGAINST(). И, конечно же, загрузите свой сервер базы данных: кэшируйте результаты в течение соответствующего времени для ваших конкретных нужд.

Во-первых, возможно, это проблема с плохо спроектированной таблицей, которая хранит строку с разделителями в одном поле вместо правильного проектирования для создания связанной таблицы. В этом случае вам следует исправить свой дизайн.

Если у вас есть поле с длинным описательным текстом (например, поле примечаний) и поиск всегда осуществляется по целому слову, вы можете выполнить полнотекстовый поиск.

Подумайте, можете ли вы потребовать от пользователей давать вам хотя бы первый символ того, что они ищут, если это обычное поле, такое как Last_name.

Рассмотрите возможность сначала выполнить поиск с точным соответствием и выполнить поиск по шаблону только в том случае, если результаты не возвращаются. Это будет работать, если у вас есть пользователи, которые могут предоставить точные совпадения. Один раз мы проделали это с поиском названий аэропортов, они возвращались очень быстро, если они вводили точное название, и медленнее, если они этого не делали.

Если вы хотите искать только строки, которые не являются словами, которые могут быть где-то в тексте, вы в значительной степени застряли с плохой производительностью.