Привет.
Есть БД mysql 5.5, категория данных - торговые точки, сайт с поиском. Объемы >10М записей в таблице для поиска.
3 таблицы:
1) tbl_markets - список магазинов;
2) tbl_params - набор поисковых признаков - ФИО владельца, адрес, геопараметры, график работы, ссылка на сайт, соц.сети - все в произвольном виде, валидации входных данных нет;
3) tbl_market_params - связь двух этих таблиц.
Пользовательский кейс - юзер заходит в поиск и может ввести в поле все что угодно: "пятерочка","Тюмень","24/7" или может выбрать и заполнить несколько конкретных параметров одновременно присущих магазину: адрес "Московская обл" | владелец "Тамара павловна". На выходе должны быть магазины без дублей с ограничением N.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
--
-- Структура таблицы `tbl_markets`
--
CREATE TABLE IF NOT EXISTS `tbl_markets` (
`market_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL
) ENGINE=InnoDB;
--
-- Дамп данных таблицы `tbl_markets`
--
INSERT INTO `tbl_markets` (`market_id`, `name`) VALUES
(1, 'Кооператив имени Пыни'),
(2, 'Пятерочка #69');
-- --------------------------------------------------------
--
-- Структура таблицы `tbl_params`
--
CREATE TABLE IF NOT EXISTS `tbl_params` (
`param_id` int(11) NOT NULL,
`description` text NOT NULL
) ENGINE=InnoDB;
--
-- Дамп данных таблицы `tbl_params`
--
INSERT INTO `tbl_params` (`param_id`, `description`) VALUES
(1, 'Владелец'),
(2, 'Адрес');
-- --------------------------------------------------------
--
-- Структура таблицы `tbl_market_params`
--
CREATE TABLE IF NOT EXISTS `tbl_market_params` (
`market_param_id` int(11) NOT NULL,
`market_id` int(11) NOT NULL,
`param_id` int(11) NOT NULL,
`value` text NOT NULL,
`active` tinyint(1) NOT NULL
) ENGINE=InnoDB;
--
-- Дамп данных таблицы `tbl_market_params`
--
INSERT INTO `tbl_market_params` (`market_param_id`, `market_id`, `param_id`, `value`) VALUES
(1, 1, 1, 'Владимир Владимирович Зеленский'),
(2, 1, 2, 'Укроссийская обл., г. Новосирия, ул. Петьки 228'),
(3, 2, 1, 'алексей сечин');
Отрабатываю вариант like "%значение%". Полнотекстовый поиск InnoDB в 5.5 не поддерживается. Переход на более новую версию mysql считать невозможным. Да и fulltext тут скорее не подойдет, т.к. в полях могут быть цифры да и всякое другое, не совсем текстовые значения - номер телефона магазина, время работы, геопараметры, ссылки и т.д.
Оптимальной считать скорость работы запроса ~5 сек.
Попытки при >10М записей приводят к краху:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select
market_id,
max(market_param_id) market_param_id #надо знать по какому именно параметру нашёлся магазин, но в принципе можно отбросить
from tbl_market_params
where 1=1
and active = 1
and value like '%USER_INPUT_DATA%'
group by
market_id
order by
1 desc
limit 10
Если построить индекс по VALUE(50) и использовать
value like 'USER_INPUT_DATA%' , отрабатывает ~ 2 сек, но нужно именно вхождение подстроки искать, например ссылка на сайт магазина может быть вида
http://pina.ru , а может
pina.ru валидации при добавлении потомучто нет - добавление ее считать невозможным, ну только в самом крайнем случае, если не найдется других вариантов оптимизации.
Но, если пользователь ищет по нескольким конкретным параметрам у магазина, тут не выходит так быстро, даже с индексом, например по адресу + любое поле:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select
market_id,
max(market_param_id) market_param_id #надо знать по какому именно параметру нашёлся магазин, но в принципе можно отбросить
from tbl_market_params tp1
where 1=1
and active = 1
and value like 'USER_INPUT_DATA_1%'
and exists (select 1 from tbl_market_params tp2 where tp1.market_id = tp2.market_id and tp2.param_id = 2 and tp2.value like 'USER_INPUT_DATA_2%' and tp2.active = 1) #таких условий может быть много, сколько выберет пользователь
group by
market_id
order by
1 desc
limit 10
Пробовал использовать поисковый движок
Sphinx , он специально заточен для быстрого поиска по текстовым полям MATCH-ем. Но там нет поддержки вложенных запросов с группировкой по одному полю, если юзер выбрал несколько параметров поиска.
Подскажите, может поменять как-то на оптимальную структуру БД, или индексы или запросы?
