Invis1ble
26.09.2013 - 01:10
Приветствую.
Есть БД с таблицей порядка 1М записей и еще одной поменьше (~35к записей), связь 1 к N.
В таблицах есть поле типа VARCHAR(254). Стоит задача искать по этим полям записи, совпадающие с шаблоном %query%, т.е. индекс не поможет:
SELECT
`site`.`host`,
`site`.`title`
FROM `sites` AS `site`
LEFT JOIN `site_aliases` AS `site_alias` ON `site_alias`.`site_id` = `site`.`id`
WHERE
`site`.`host` LIKE '%query%'
OR `site_alias`.`host` LIKE '%query%'
ORDER BY (
`site`.`host` LIKE 'query%'
OR `site_alias`.`host` LIKE 'query%'
) DESC
LIMIT 15;
(есть еще 3-я таблица, которая джойнится для фильтрации по флагу, но на крайняк можно денормализовать)
на данный момент на запрос уходит около 4 сек, что конечно же недопустимо, т.к. запросы поступают очень часто (поиск на аяксе) и пользователь может уснуть, дожидаясь результата :)
Есть у кого какие идеи, как можно исправить положение?
Кстати, сторонние приблуды типа Sphinx подходят для решения такой задачи?PS. Задача в скором времени расширится до поиска не только по host, но и по title, так что запрос изменится в сторону усложнения.
PPS. MySQL 5.5, движок таблиц InnoDB
_____________
Профессиональная разработка на заказЯ на GitHub |
второй профиль
Invis1ble
26.09.2013 - 18:03
T1grOK
26.09.2013 - 20:15
| Цитата (MiksIr @ 26.09.2013 - 16:13) |
| Так чем сфинкс неподходит? |
Он же и спрашивает сфинск можно к этому делу примостырить.
Да можно.
_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
Invis1ble
26.09.2013 - 22:22
FatCat
27.09.2013 - 00:27
| Цитата (Invis1ble @ 26.09.2013 - 00:10) |
| какие идеи, как можно исправить положение? |
Добавить таблицу кеширования результатов поиска. Наверняка поиски будут многократно дублироваться.
_____________
Бесплатному сыру в дырки не заглядывают...
Invis1ble
27.09.2013 - 07:55
MiksIrМожно поподробнее, чем дублирование в MyISAM может помочь? Искать также, LIKE ? И для чего FTP ?
FatCatдублироваться могут, но это будет ОГРОМНЕЙШАЯ таблица и пока она наполнится... Вобщем не вариант. Юзер вводит символы в поле поиска и при каждом нажатии летит ajax-запрос.
_____________
Профессиональная разработка на заказЯ на GitHub |
второй профиль
Игорь_Vasinsky
27.09.2013 - 07:57
| Цитата |
| Юзер вводит символы в поле поиска и при каждом нажатии летит ajax-запрос. |
для начала наверно - ограничить до 2-3 мин кол-во введённых символов - для запроса, сделать задержку запроса на 1сек после ввода (а то юзер опечатался - а ты уже запрос послал)
ты живой поиск делаешь или это просто auto_complete ?
таблицы регулярно пополняются данными ?
можно хранить результаты запросов
_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker
Invis1ble
27.09.2013 - 08:03
Игорь_Vasinsky
| Цитата |
| ограничить до 2-3 мин кол-во введённых символов |
3 стоит
поиск по символам по 5 полям (есть еще 2 поля типа host и надо буте искать по title) с примитивным ранжированием, что-то типа автокомплита. Юзер вводит символы и появляется выпадающий список.
| Цитата |
| сделать задержку запроса на 1сек после ввода (а то юзер опечатался - а ты уже запрос послал) |
задержка не нужна, для юзера должно быть все мгновенно. Можно поставить максимум ~0.15 сек я думаю.
_____________
Профессиональная разработка на заказЯ на GitHub |
второй профиль
T1grOK
27.09.2013 - 09:50
| Цитата (Invis1ble @ 27.09.2013 - 04:03) |
| Можно поставить максимум ~0.15 сек я думаю. |
Как по мне, лучше поставить задержку побольше. Скажем за исходную позицию учесть среднюю скорость набора 200-250 знаков в минуту(или с частотой превышающей 0.25, 0.3 секунды), чтоб больше отсеять "медленных пользователей". Будет больше задержка(0.25, 0.3 с.), будет меньше нагружаться сервер, соответственно быстрей выполняться поиск. Тобишь нужно найти оптимальный вариант.
К тому же можно на стороне клиента создать кеш, разумного размера конечно, чтоб еще отсеять возможные повторения поисковой строки(при повторном наборе или при delete, backspace). Чтоб вообще на сервер не посылать ничего и не лазить к нему даже за кешем.
_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
NitroGenerate
27.09.2013 - 13:37
На работе делал поиск по бд с 500 тыс записями через лайк, искал по нескольким полям текст, логикой OR.
Движок InnoDB, мало того, запрос выполнялся ~3 сек, так результат его был очень далек от релевантности, тоесть на первые места выкидывал то, что по логике должно быть далеко внизу.
Спустя несколько дней мучений, создания псевдо таблиц для поиска, пришлось цеплять сфинкса. И о чудо, он решил сразу все мои проблемы. Каждые 10 мин сканил основную бд и выцеплял и сохранял у себя нужные поля (поля для поиска + поле с id для дальнейшего зацепа).
Результат, релевантный поиск, время поиска ~0.1 сек. (в 30 раз быстрее!)
Сфинкс для того и делали, что бы искать по тексту. Более того, многие high load проекты его активно используют начиная habrahabr, avito и тд
Invis1ble
29.09.2013 - 01:48
| Цитата (T1grOK @ 27.09.2013 - 08:50) |
| Цитата (Invis1ble @ 27.09.2013 - 04:03) | | Можно поставить максимум ~0.15 сек я думаю. |
Как по мне, лучше поставить задержку побольше. Скажем за исходную позицию учесть среднюю скорость набора 200-250 знаков в минуту(или с частотой превышающей 0.25, 0.3 секунды), чтоб больше отсеять "медленных пользователей". Будет больше задержка(0.25, 0.3 с.), будет меньше нагружаться сервер, соответственно быстрей выполняться поиск. Тобишь нужно найти оптимальный вариант.
К тому же можно на стороне клиента создать кеш, разумного размера конечно, чтоб еще отсеять возможные повторения поисковой строки(при повторном наборе или при delete, backspace). Чтоб вообще на сервер не посылать ничего и не лазить к нему даже за кешем.
|
Это все понятно, но это уже второстепенно. Надо сначала уменьшить время выполнения запроса.
Сделал отдельную таблицу MyISAM, в которую записал результаты выборки без WHERE. Время запроса сократилось до 1 сек примерно, но все равно это долго.
Про FTP так и не понял
PS. Поковырял Sphinx, пока что не удается добиться нужного ранжирования. Да и время поиска подозрительно долгое, более 1 сек...
_____________
Профессиональная разработка на заказЯ на GitHub |
второй профиль
DedMorozzz
29.09.2013 - 12:25
| Цитата |
| Кстати, сторонние приблуды типа Sphinx подходят для решения такой задачи? |
Более чем
Ставь индексы в сфинксе на нужные поля. Обнови ключи и всё будет гуд. В общем кури маны
_____________
Если не говорить пользователям, что Linux это "Сложно и страшно", то им совершенно всё равно, в чём не разбираться
Быстрый ответ:
Powered by dgreen
Здесь расположена полная версия этой страницы.