php mysql поиск по словам
Поиск по тексту в MySQL
Недавно начались некоторые опыты с морфологией, чтобы их произвести и обкатать некоторые алгоритмы, пришлось воспользоваться словарем от OpenCorpora. В исходном виде табличка со словарем включает около 405 тысяч строк, в каждой из них информация по формам слова, его лемме, род, время, падеж, одушевленное или нет, всё это занимает около 489 мегабайт.
Мне понадобилось вытаскивать по конкретному слову его данные, для этого составил табличку такого типа
| id | lemma_id | word | gram | gram_descr |
|---|---|---|---|---|
| 1 | 1 | клавиатура | . | . |
Можно прибегнуть к системам полнотекстового поиска, может так и сделаю, но меня первым делом смущает работа по http некоторых таких систем, если надо будет 1000 запросов в секунду кидать, ну я их еще проверю, еще планирую потестировать всякие MongoDB.
Итак, поехали, попробуем получить клавиатура. Как видим, 2.4 сек, иногда до 3.4 доходило, это очень ужасно.
Теперь попробуем по точному совпадению key=>val. Ситуация особо не меняется, странно, но даже дольше выполняется, несколько раз перезапускал и оно только вот так, 2.5 сек, такой результат еще более ужасный.
Теперь попробуем такую мощную штуку, как полнотекстовый поиск, для этого нужно добавить полнотекстовый индекс к полю word.
К слову, у меня этот индекс был уже изначально на момент тестирования, выполняем запрос
И о чудо, 0.0004 сек, вот такой результат более чем приличный
Тут видно, что предлог «за» есть в таблице..
Но так не сможет его найти, т.к. есть ограничение
Давайте попробуем снять это ограничение, пропишем в конфиг [mysqld]
Ничего не выходит, наверно нельзя по одной или двум буквам искать, значит нам это не совсем подходит. Но мы можем например выделить все слова до 3х символов в отдельную таблицу и там будет искать их быстро обычными методами.
В любом случае, этот метод будет достаточно хорош для поиска по текстам на сайте, например по описаниям товаров, если особенно на обычном хостинге, где нет Sphinx, ElasticSearch и другого такого.
Определим сколько у нас максимальное по длине слово
Получим 35, слова такие конечно редкие, но мы должны предусмотреть максимальную базу данных
Попробуем тип поля сделать не text, а varchar 50, а потом поискать стандартным способом через like, ничего не поменялось.
Попробуем сравнение изменить на utf8_bin
С Like ситуация не поменялась, с полным WHERE тоже, но теперь появилась возможность сделать простой индекс не полнотекстовый для поля word
После добавления индекса к полю, у меня стало искать за доли секунды
При таком раскладе, скорость получения данных по словам будет 2500 слов в секунду, не плохо. Правда сравнение utf8_bin накладывает некоторые ограничения, например нет регистронезависимого поиска, нужно при выборке и хранении понижать регистр, хотя тут вроде итак всё в нижнем регистре.
Попробуем поменять сравнение в поле на utf8_unicode_ci, и снова перестроить индекс может там не сильно будет потеря, да, MariaDB вполне справляется, то что пишут на форумах про ускорение в сравнении bin наверно уже устарело.
Теперь попробую еще одну идею, добавить в таблицу поле length и записать в него длину, сделаем его INT(2), ну максимум у нас все равно слово может быть 50 букв, хотя это там даже не слово, а какие то длинные названия. И сделаем поле индексированным.
Сделаем такой запрос для его заполнения
А теперь попробуем поискать вот так
Нет, это не дало результата должного, уже нет такого критичного времени, которое может оптимизироваться, но идея заключалась в том, чтобы выбрать по индексированному полю с длиной строки, а потом из этого уже по слову искать.
Выводы: если нужна выборка по текстовому полю, например ЧПУ страницы сайта, то надо использовать поле varchar фиксированного размера и добавлять к нему индекс, как видите, с миллионами записей справляется идеально. Если же нужен поиск по текстам описаний (text) с вхождением слов, то лучше использовать полнотекстовый поиск, он достаточно быстрый.
Паттерны проектирования
Недавно я начал читать книжку по паттернам проектирования, это такие общеприняттые и…
Полнотекстовый поиск MySQL
Здравствуйте, уважаемые читатели блога LifeExample, задача полнотекстового поиска по таблицам базы данных, является неотъемлемой, практически, для каждого веб-ресурса. Все новички рано или поздно сталкиваются с вопросом, «как реализовать полнотекстовый поиск по сайту?» конечно сначала задача всплывает несколько иначе: «как сделать поиск по сайту?».
Отличие обычного поиска от полнотекстового в том что, при первом искать сущности сайта, будь то товары магазина или статьи блога, можно по конкретному параметру, например по дате добавления. Полнотекстовый поиск, в свою очередь, подразумевает поиск по совпадающим ключевым словам запроса и текста искомого товара или статьи.
Самым наглядным примером полнотекстового поиска являются поисковые системы, такие как yandex.ru или google.ru, при вводе запроса «Полнотекстовый поиск MySQL» в поисковую строку система разобьет фразу на три слова:
Затем проанализирует имеющиеся сущности, в данном случае это набор проиндексированных страниц сайтов, на наличие совпадений с каждым словом. Те страницы, которые имеют хотя бы одно из слов всей фразы либо их словоформ, будут выведены в поисковую выдачу.
Надо заметить, что поисковые системы обладают множеством алгоритмов позволяющих найти наиболее релевантную информацию по введенной фразе, но мы сегодня создадим самый простейший полнотекстовый поиск с помощью MySQL.
Как сделать полнотекстовый поиск MySQL
Какой бы не казалась задача полнотекстового поиска сложной, решается она очень быстро и просто. Беря в расчет, что сегодня большинство сайтов используют в качестве хранилища информации базу данных MySQL, то далее речь пойдет о реализации поиска по сайту именно по таблицам MySQL.
Реализовать поиск по таблицам MySQL можно разными способами:
Первые два оператора самый примитивный вариант для поиска по сайту, и кроме того очень медлительный и требующий большой производительности сервера.
Поиск по сайту с помощью оператора LIKE
Например, реализовать поиск по таблице статей, используя оператор LIKE, можно следующим запросом:
Данный запрос выдаст все записи, т.е. все статьи в тексте которых встретится слово «новость», если в содержании самой статьи нет данного слова, но оно в присутствует в заголовке, статья не будет найдена.
Чтобы искать статьи также и по совпадению в заголовках, можно усовершенствовать запрос:
Этот SQL запрос учитывает два текстовых поля, по которым произойдет поиск статьи, но использует уже несколько операторов LIKE, а это двойная нагрузка на сервер. Кроме того если пользователь запросит статьи по фразе «новость дня», то получи те статьи, которые содержат исключительно именно такую фразу: «новость дня».
MySql match – все что нужно для поиска по сайту
В арсенале программиста, использующего mysql, имеется стандартный инструмент для полнотекстового поиска MySql – операторы: MATCH и AGAINST.
Возьмем, к примеру, задачу поиска товаров по базе интернет магазина. Как правило, товар имеет ряд текстовых характеристик, таких как: название, описание, артикул, ключевые слова и др.
Используя для реализации полнотекстового поиска MATCH и AGAINST запрос может выглядит так:
При этом, для работоспособности запроса должен существовать индекс таблицы товаров с типом FULLTEXT, объединяющий в себе перечень полей для поиска:
Имя индекса может быть любым, а в полях должны быть перечислены, все те, которые будут участвовать в поиске. При данном индексе, показанном на картинке, нельзя делать выборку только по одному из указанных полей.
Искать не точное соответствие по введенной фразе и соответствие по ее частям можно таким образом:
При таком запросе в результат попадут записи имеющие вхождения фрагментов слов ful и nam
Надо учесть, что при работе с MATCH запрещено использовать в качестве именно полей зарезервированные слова.
Существует список запрещенных к использованию слов, то есть следующими словами нельзя называть поля таблиц, чтобы потом использовать их в полнотекстовом поиске MySql:
| a’s | able | about | above | according |
| accordingly | across | actually | after | afterwards |
| again | against | ain’t | all | allow |
| allows | almost | alone | along | already |
| also | although | always | am | among |
| amongst | an | and | another | any |
| anybody | anyhow | anyone | anything | anyway |
| anyways | anywhere | apart | appear | appreciate |
| appropriate | are | aren’t | around | as |
| aside | ask | asking | associated | at |
| available | away | awfully | be | became |
| because | become | becomes | becoming | been |
| before | beforehand | behind | being | believe |
| below | beside | besides | best | better |
| between | beyond | both | brief | but |
| by | c’mon | c’s | came | can |
| can’t | cannot | cant | cause | causes |
| certain | certainly | changes | clearly | co |
| com | come | comes | concerning | consequently |
| consider | considering | contain | containing | contains |
| corresponding | could | couldn’t | course | currently |
| definitely | described | despite | did | didn’t |
| different | do | does | doesn’t | doing |
| don’t | done | down | downwards | during |
| each | edu | eg | eight | either |
| else | elsewhere | enough | entirely | especially |
| et | etc | even | ever | every |
| everybody | everyone | everything | everywhere | ex |
| exactly | example | except | far | few |
| fifth | first | five | followed | following |
| follows | for | former | formerly | forth |
| four | from | further | furthermore | get |
| gets | getting | given | gives | go |
| goes | going | gone | got | gotten |
| greetings | had | hadn’t | happens | hardly |
| has | hasn’t | have | haven’t | having |
| he | he’s | hello | help | hence |
| her | here | here’s | hereafter | hereby |
| herein | hereupon | hers | herself | hi |
| him | himself | his | hither | hopefully |
| how | howbeit | however | i’d | i’ll |
| i’m | i’ve | ie | if | ignored |
| immediate | in | inasmuch | inc | indeed |
| indicate | indicated | indicates | inner | insofar |
| instead | into | inward | is | isn’t |
| it | it’d | it’ll | it’s | its |
| itself | just | keep | keeps | kept |
| know | known | knows | last | lately |
| later | latter | latterly | least | less |
| lest | let | let’s | like | liked |
| likely | little | look | looking | looks |
| ltd | mainly | many | may | maybe |
| me | mean | meanwhile | merely | might |
| more | moreover | most | mostly | much |
| must | my | myself | name | namely |
| nd | near | nearly | necessary | need |
| needs | neither | never | nevertheless | new |
| next | nine | no | nobody | non |
| none | noone | nor | normally | not |
| nothing | novel | now | nowhere | obviously |
| of | off | often | oh | ok |
| okay | old | on | once | one |
| ones | only | onto | or | other |
| others | otherwise | ought | our | ours |
| ourselves | out | outside | over | overall |
| own | particular | particularly | per | perhaps |
| placed | please | plus | possible | presumably |
| probably | provides | que | quite | qv |
| rather | rd | re | really | reasonably |
| regarding | regardless | regards | relatively | respectively |
| right | said | same | saw | say |
| saying | says | second | secondly | see |
| seeing | seem | seemed | seeming | seems |
| seen | self | selves | sensible | sent |
| serious | seriously | seven | several | shall |
| she | should | shouldn’t | since | six |
| so | some | somebody | somehow | someone |
| something | sometime | sometimes | somewhat | somewhere |
| soon | sorry | specified | specify | specifying |
| still | sub | such | sup | sure |
| t’s | take | taken | tell | tends |
| th | than | thank | thanks | thanx |
| that | that’s | thats | the | their |
| theirs | them | themselves | then | thence |
| there | there’s | thereafter | thereby | therefore |
| therein | theres | thereupon | these | they |
| they’d | they’ll | they’re | they’ve | think |
| third | this | thorough | thoroughly | those |
| though | three | through | throughout | thru |
| thus | to | together | too | took |
| toward | towards | tried | tries | truly |
| try | trying | twice | two | un |
| under | unfortunately | unless | unlikely | until |
| unto | up | upon | us | use |
| used | useful | uses | using | usually |
| value | various | very | via | viz |
| vs | want | wants | was | wasn’t |
| way | we | we’d | we’ll | we’re |
| we’ve | welcome | well | went | were |
| weren’t | what | what’s | whatever | when |
| whence | whenever | where | where’s | whereafter |
| whereas | whereby | wherein | whereupon | wherever |
| whether | which | while | whither | who |
| who’s | whoever | whole | whom | whose |
| why | will | willing | wish | with |
| within | without | won’t | wonder | would |
| wouldn’t | yes | yet | you | you’d |
| you’ll | you’re | you’ve | your | yours |
| yourself | yourselves | zero |
Надо сказать, я потратил не один час на то чтобы понять, почему мой полнотекстовый поиск MySql то работает, то не очень, а ведь все дело оказалось именно в совпадении наименований полей с зарезервированными словами.
Как я и обещал, в начале статьи, задача реализации полнотекстового поиска в MySQL сводится к минимуму действий:
Возникли вопросы? Пишите в комментариях! Подписывайтесь на RSS рассылку, впереди много интересных и полезных статей.