Ускоряем WHERE lower(haystack_column) LIKE '%needle%'

[poige]

Во-1-х, несколько распространённых заблуждений:

1. index по haystack_column ускорит выборку
   
2. index наивно создаётся по haystack_column as is
   

С 1-м, очевидно, всю малину портит начальный '%'. Второе тоже очевидно, но не для всех - тут требуется функциональный индекс, с тем самым lower(). Впрочем, см. пункт 1. Вся конструкция несомненно полезна с точки

Comments

[honeyman]

А иголка всегда одна и та же (хотя у тебя по задаче и стог-то константный :) )? Ну так говно вопрос, CREATE INDEX needles_idx ON hays((lower(haystack) LIKE '%needle%'));

> Ну так говно вопрос, CREATE INDEX needles_idx ON

[poige]

Ну чтобы остальным понятно было - UPD. :)

[mentatxx]

В постгресе например, можно сделать индекс по выражению
CREATE INDEX ON films ((lower(title)));
http://www.postgresql.org/docs/9.1/static/sql-createindex.html

А вот как побороть начальный % - хз

> В постгресе например, можно сделать индекс по выражен

[poige]

Это называется «функциональный индекс»: «… но тут требуется функциональный индекс - с тем самым lower() …»

> А вот как побороть начальный % - хз

Думайте, чо…

[mentatxx]

А с хрена ли каммент автоматом как спам помечается ?

> А с хрена ли каммент автоматом как спам помечается ?

[poige]

http://www.livejournal.com/manage/settings/?cat=privacy

Spam Protection: «Comments containing a link to a non-whitelisted domain will be marked as spam and moved to special section.»

[mentatxx]

Вообще если есть задача точного поиска слова в полнотекстовом поиске - можно заюзать GIN / GIST индексы, если же надо сделать человеческий поиск - стоит прикрутить sphinx.
Индекса по любым подстрокам каждой строки в таблице имхо не существует ни в одной базе.

> имхо не существует ни в одной базе

[poige]

А никто и не утверждает.

Re: > имхо не существует ни в одной базе

[mentatxx]

Вообщем взял попкорн и подписался на камменты )
зы. как капча задалбывает ...

[npocmu]

FULLTEXT search в MS SQL с прошлого века существует.

[zegna]

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

> никаким функциональным индексом проблему

[poige]

Ладно, коли дело идёт так вяло, скоро раскрою карты, так сказать. :-)

[poige]

http://poige.livejournal.com/562406.html?thread=3757030#t3757030