Есть задача реализовать удобный поиск в системе. Нашел очень удачное решение на сайте mobile.de:

нажмите кнопку "показать" и см. закладку слева "Ограничить поиск"

Суть примерно такова, что есть 1 млн записей в базе данных, быстрый поиск по данным записям возможен по примерно 15 категориям. В некоторых категориях может быть до 100 и более значений.
Каждое значение категории имеет подсказку количества значений если будет выбрана данная категория.

Делать насчет по общей таблицы записей не представляется возможным во вразумительное время.

Для разбора данной ситуации предлагаю упрощенный вариант с четырьмя измерениями (Бренд, Категория, Модель, Продавец), для решения данной задачи предлагается создать суммирующую таблицу, в которую будут помещаться конечные "предрассчитаные" значения поиска.

-- creating table
CREATE TABLE PRT_SEARCH_HELPER (
    COUNT_       INTEGER,
    BRAND_ID     NUMERIC(18,0),
    CATEGORY_ID  NUMERIC(18,0),
    MODEL_ID     NUMERIC(18,0),
    SELLER_ID    NUMERIC(18,0)
);

Про индексы пока ничего не говорю.

Для построения одной категории (в примрере сумм по категориям) будет делаться запрос вида

select h.category_id, sum(h.count_) from prt_search_helper h where
h.brand_id=ххх
and h.seller_id=ххх
group by
h.category_id

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

Чтобы грубо оценить объем данных в этой таблице необходимо перемножить количества возможных значения измерений, это покроет все комбинации данных значений допустим
брендов у нас 100
моделей 1000
продавцов 100
категорий 100
получается, около 1 млрд записей, допустим я сделал избыточной данную информацию и часть записей будет с нулевым количеством записей, их можно выкинуть, пусть останется 5-10 млн записей, но это все равно многовато. При 5 млн записей приведный выше запрос отрабатывает за 300мс на машине класса Core 2 Duo 2.5 Ггц, а таких запросов надо делать столько сколько категорий. В итоге получается все равно медленно, ообенно на нагруженной системе.

Уважамые коллеги, конибудь решал подобного рода задачи, если решали то в каком направлении дальше можно оптимизировать данную стуктуру поисковой таблицыее наполнения, построения запросов?

Здравствуйте, Аноним, Вы писали:

А>Уважамые коллеги, конибудь решал подобного рода задачи, если решали то в каком направлении дальше можно оптимизировать данную стуктуру поисковой таблицыее наполнения, построения запросов?

Вы привели почти все исходные данные, кроме, возможно, главного: используемой СУБД и версии. Разные СУБД предлагают свои средства для решения подобных задач.

Здравствуйте, Аноним, Вы писали:

А>Есть задача реализовать удобный поиск в системе. Нашел очень удачное решение на сайте mobile.de:

А>нажмите кнопку "показать" и см. закладку слева "Ограничить поиск"

А>Суть примерно такова, что есть 1 млн записей в базе данных, быстрый поиск по данным записям возможен по примерно 15 категориям. В некоторых категориях может быть до 100 и более значений.
А>Каждое значение категории имеет подсказку количества значений если будет выбрана данная категория.

а почему не использовать олап сервер какой? он заранее все посчитает и быстро будет выдавать.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, Аноним, Вы писали:

А>>Уважамые коллеги, конибудь решал подобного рода задачи, если решали то в каком направлении дальше можно оптимизировать данную стуктуру поисковой таблицыее наполнения, построения запросов?

W>Вы привели почти все исходные данные, кроме, возможно, главного: используемой СУБД и версии. Разные СУБД предлагают свои средства для решения подобных задач.

В текущей постановке это Firebird возможно потом будет Mysql или postgresql.

Здравствуйте, Аноним, Вы писали:

А>Здравствуйте, Аноним, Вы писали:

А>>Есть задача реализовать удобный поиск в системе. Нашел очень удачное решение на сайте mobile.de:

А>>нажмите кнопку "показать" и см. закладку слева "Ограничить поиск"

А>>Суть примерно такова, что есть 1 млн записей в базе данных, быстрый поиск по данным записям возможен по примерно 15 категориям. В некоторых категориях может быть до 100 и более значений.
А>>Каждое значение категории имеет подсказку количества значений если будет выбрана данная категория.

А>а почему не использовать олап сервер какой? он заранее все посчитает и быстро будет выдавать.

Если Вы говорите об системах типа Olap/DWH, то фактически я эту таблицу сделал за OLAP я могу построить измерения,
но на мой взгляд это не даст нужную производительность, это не отчетная система и речь идет о миллисекундах.

Здравствуйте, Аноним, Вы писали:

А>В текущей постановке это Firebird возможно потом будет Mysql или postgresql.

В этих СУБД встроенных средств нет, но с подходами к реализации можно ознакомиьться тут http://en.wikipedia.org/wiki/Materialized_view, ссылки в конце.

Здравствуйте, senna, Вы писали:

S>Если Вы говорите об системах типа Olap/DWH, то фактически я эту таблицу сделал за OLAP я могу построить измерения,
S>но на мой взгляд это не даст нужную производительность, это не отчетная система и речь идет о миллисекундах.

Можно поинтересоваться, в каком не отчётном сценарии необходимо чтоб запрос возвращал 1000 или хотя бы 100 записей?

Здравствуйте, KRA, Вы писали:

KRA>Здравствуйте, senna, Вы писали:

S>>Если Вы говорите об системах типа Olap/DWH, то фактически я эту таблицу сделал за OLAP я могу построить измерения,
S>>но на мой взгляд это не даст нужную производительность, это не отчетная система и речь идет о миллисекундах.

KRA>Можно поинтересоваться, в каком не отчётном сценарии необходимо чтоб запрос возвращал 1000 или хотя бы 100 записей?

Если речь идёт о поиске, то какой смысл возвращать больше 10-20 записей?

Здравствуйте, Аноним, Вы писали:

А>Есть задача реализовать удобный поиск в системе. Нашел очень удачное решение на сайте mobile.de:

А>нажмите кнопку "показать" и см. закладку слева "Ограничить поиск"

Почему-то не работает ссылка, поэтому не совсем понятно.

А>Суть примерно такова, что есть 1 млн записей в базе данных, быстрый поиск по данным записям возможен по примерно 15 категориям. В некоторых категориях может быть до 100 и более значений.
А>Каждое значение категории имеет подсказку количества значений если будет выбрана данная категория.

Правильно ли я понимаю, что нужно по сути для 15 категорий находить количество товаров? Если так, то лучше их считать тригером при добавлении товара в категорию.

А>Делать насчет по общей таблицы записей не представляется возможным во вразумительное время.

А>Для разбора данной ситуации предлагаю упрощенный вариант с четырьмя измерениями (Бренд, Категория, Модель, Продавец), для решения данной задачи предлагается создать суммирующую таблицу, в которую будут помещаться конечные "предрассчитаные" значения поиска.


А>

А>-- creating table
А>CREATE TABLE PRT_SEARCH_HELPER (
А>    COUNT_       INTEGER,
А>    BRAND_ID     NUMERIC(18,0),
А>    CATEGORY_ID  NUMERIC(18,0),
А>    MODEL_ID     NUMERIC(18,0),
А>    SELLER_ID    NUMERIC(18,0)
А>);

А>

Правильно ли я понимаю, что по модели тоже нужен поиск аля "сколько штук такой-то модели у такого то продавца"?
Если нет, зачем тут поле MODEL_ID?

А>

А>select h.category_id, sum(h.count_) from prt_search_helper h where
А>h.brand_id=ххх
А>and h.seller_id=ххх
А>group by
А>h.category_id
А>

А>для того, чтобы запрос работал быстро необходимо сдлеать составной индекс по brand_id и seller_id.
и category_id

А>Чтобы грубо оценить объем данных в этой таблице необходимо перемножить количества возможных значения измерений, это покроет все комбинации данных значений допустим
А>брендов у нас 100
А>моделей 1000
А>продавцов 100
А>категорий 100
А>получается, около 1 млрд записей, допустим я сделал избыточной данную информацию и часть записей будет с нулевым количеством записей, их можно выкинуть, пусть останется 5-10 млн записей, но это все равно многовато. При 5 млн записей приведный выше запрос отрабатывает за 300мс на машине класса Core 2 Duo 2.5 Ггц, а таких запросов надо делать столько сколько категорий. В итоге получается все равно медленно, ообенно на нагруженной системе.

Не ясно почему нужно будет делать по запросу на категорию. Запрос же сразу считает количество для всех категорий.

Не уверен, что правильно интерпретирую сущности,
1. разве может одна модель относяться больше чем к 2-3 категориям?
2. может одна модель соответсвовать аж 100 разных брендов?

Это я к тому, что, возможно, оценка в 1 млрд сильно завышена.

А>Уважамые коллеги, конибудь решал подобного рода задачи, если решали то в каком направлении дальше можно оптимизировать данную стуктуру поисковой таблицыее наполнения, построения запросов?
Я решал подобную задачу путём денормализации данных. Грубо говоря, заранее готовил данные в удобном для запросов виде (так чтоб при запросе использовалась только фильтрация и не использовались групировки). Давненько это было, но по памяти на рабочей системе данных было миллионов 10. На подобного класса машине запросы работалы довольно шустро, порядка 0.1 сек вместе с рендерингом веб страницы.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, Аноним, Вы писали:

А>>В текущей постановке это Firebird возможно потом будет Mysql или postgresql.

W>В этих СУБД встроенных средств нет, но с подходами к реализации можно ознакомиьться тут http://en.wikipedia.org/wiki/Materialized_view, ссылки в конце.
что такое материализованное представление я знаю, но оно проблемы не решает: фактически я сделал суммирующую таблицу это и есть самодельное материализованное представление. Я пока речь не веду как заполнять это

Здравствуйте, KRA, Вы писали:

KRA>Здравствуйте, Аноним, Вы писали:

А>>Есть задача реализовать удобный поиск в системе. Нашел очень удачное решение на сайте mobile.de:

А>>нажмите кнопку "показать" и см. закладку слева "Ограничить поиск"

KRA>Почему-то не работает ссылка, поэтому не совсем понятно.

А>>Суть примерно такова, что есть 1 млн записей в базе данных, быстрый поиск по данным записям возможен по примерно 15 категориям. В некоторых категориях может быть до 100 и более значений.
А>>Каждое значение категории имеет подсказку количества значений если будет выбрана данная категория.

KRA>Правильно ли я понимаю, что нужно по сути для 15 категорий находить количество товаров? Если так, то лучше их считать тригером при добавлении товара в категорию.

А>>Делать насчет по общей таблицы записей не представляется возможным во вразумительное время.

А>>Для разбора данной ситуации предлагаю упрощенный вариант с четырьмя измерениями (Бренд, Категория, Модель, Продавец), для решения данной задачи предлагается создать суммирующую таблицу, в которую будут помещаться конечные "предрассчитаные" значения поиска.


А>>

А>>-- creating table
А>>CREATE TABLE PRT_SEARCH_HELPER (
А>>    COUNT_       INTEGER,
А>>    BRAND_ID     NUMERIC(18,0),
А>>    CATEGORY_ID  NUMERIC(18,0),
А>>    MODEL_ID     NUMERIC(18,0),
А>>    SELLER_ID    NUMERIC(18,0)
А>>);

А>>

KRA>Правильно ли я понимаю, что по модели тоже нужен поиск аля "сколько штук такой-то модели у такого то продавца"?
KRA>Если нет, зачем тут поле MODEL_ID?

А>>

А>>select h.category_id, sum(h.count_) from prt_search_helper h where
А>>h.brand_id=ххх
А>>and h.seller_id=ххх
А>>group by
А>>h.category_id
А>>

А>>для того, чтобы запрос работал быстро необходимо сдлеать составной индекс по brand_id и seller_id.
KRA>и category_id

А>>Чтобы грубо оценить объем данных в этой таблице необходимо перемножить количества возможных значения измерений, это покроет все комбинации данных значений допустим
А>>брендов у нас 100
А>>моделей 1000
А>>продавцов 100
А>>категорий 100
А>>получается, около 1 млрд записей, допустим я сделал избыточной данную информацию и часть записей будет с нулевым количеством записей, их можно выкинуть, пусть останется 5-10 млн записей, но это все равно многовато. При 5 млн записей приведный выше запрос отрабатывает за 300мс на машине класса Core 2 Duo 2.5 Ггц, а таких запросов надо делать столько сколько категорий. В итоге получается все равно медленно, ообенно на нагруженной системе.

KRA>Не ясно почему нужно будет делать по запросу на категорию. Запрос же сразу считает количество для всех категорий.

KRA>Не уверен, что правильно интерпретирую сущности,
KRA>1. разве может одна модель относяться больше чем к 2-3 категориям?
KRA>2. может одна модель соответсвовать аж 100 разных брендов?

KRA>Это я к тому, что, возможно, оценка в 1 млрд сильно завышена.

А>>Уважамые коллеги, конибудь решал подобного рода задачи, если решали то в каком направлении дальше можно оптимизировать данную стуктуру поисковой таблицыее наполнения, построения запросов?
KRA>Я решал подобную задачу путём денормализации данных. Грубо говоря, заранее готовил данные в удобном для запросов виде (так чтоб при запросе использовалась только фильтрация и не использовались групировки). Давненько это было, но по памяти на рабочей системе данных было миллионов 10. На подобного класса машине запросы работалы довольно шустро, порядка 0.1 сек вместе с рендерингом веб страницы.


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

Марка
* VW (187951)
* Mercedes-Benz (122658)
* BMW (105710)
* Opel (98532)
* Audi (97912)
* Расширить выборку
Тип топлива
* Бензиновый (613303)
* Дизельный (482332)
* Сжиженный нефтяной газ (9039)
* Расширить выборку
Категория
* Лимузин (417215)
* Универсал (250705)
* Минивэн/микроавтобус (120663)
* Расширить выборку
Подкатегория
* Демонстрационное транспортное средство (53773)
* Служебный автомобиль (36418)
* Предварительная регистрация (8268)
* Расширить выборку
Мощность
* 74 кВт — 86 кВт (166183)
* 85 кВт — 97 кВт (95409)
* 96 кВт — 111 кВт (186828)
* 110 кВт — 148 кВт (164748)
* 147 кВт — 185 кВт (77247)
* Расширить выборку

....
это первый шаг. Вот если выбрать хоть что то из этого, то все остальные пересчитаются с учетом этого!
то есть если выбрать лимузин, то значение остальных будут совсем другими.


понаблюдать все это можно на сайте www.mobile.de если попробовать поискать автомобиль.

Здравствуйте, Аноним, Вы писали:

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

Кажется начал понимать, но ещё не до конца:
1. Сколько категорий у Вас всего есть?
2. Могут ли категории создаваться динамически?
3. Что за количество хранится в поле _Count таблицы PRT_SEARCH_HELPER? Я почему спрашиваю, мне не понятно, может ли там быть значение не единица. По виду похоже на количество экземпляров такой-то модели, у такого то продавца. Но тогда не ясно зачем поле бренд, разве одно не однозначно определяется моделью? В общем тут неясность у меня с предметной областью.

Здравствуйте, Аноним, Вы писали:

А>что такое материализованное представление я знаю, но оно проблемы не решает: фактически я сделал суммирующую таблицу это и есть самодельное материализованное представление. Я пока речь не веду как заполнять это

Тогда объясни получше в чем проблема.

Здравствуйте, wildwind, Вы писали:

W>Здравствуйте, Аноним, Вы писали:

А>>что такое материализованное представление я знаю, но оно проблемы не решает: фактически я сделал суммирующую таблицу это и есть самодельное материализованное представление. Я пока речь не веду как заполнять это

W>Тогда объясни получше в чем проблема.

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

Здравствуйте, KRA, Вы писали:

KRA>Здравствуйте, Аноним, Вы писали:

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

KRA>Кажется начал понимать, но ещё не до конца:
KRA>1. Сколько категорий у Вас всего есть?
KRA>2. Могут ли категории создаваться динамически?
KRA>3. Что за количество хранится в поле _Count таблицы PRT_SEARCH_HELPER? Я почему спрашиваю, мне не понятно, может ли там быть значение не единица. По виду похоже на количество экземпляров такой-то модели, у такого то продавца. Но тогда не ясно зачем поле бренд, разве одно не однозначно определяется моделью? В общем тут неясность у меня с предметной областью.

1. В начале топика описано, сколько категорий, сколько в каждой из них значений, и до каких упрощено
2. Нет
3. Это количество значений которое получается при выборе конретно бренда, модели, категории, продавца.
Соотвественно могут быть строки только с одним полем из бренд, модель, категория, продавец, или комбинированые.
Бренд нужен для для оптимизации, вычисление (схлопывание) из значений модели будет более долгим.

Здравствуйте, senna, Вы писали:

S>Здравствуйте, KRA, Вы писали:

KRA>>3. Что за количество хранится в поле _Count таблицы PRT_SEARCH_HELPER? Я почему спрашиваю, мне не понятно, может ли там быть значение не единица. По виду похоже на количество экземпляров такой-то модели, у такого то продавца. Но тогда не ясно зачем поле бренд, разве одно не однозначно определяется моделью? В общем тут неясность у меня с предметной областью.

S>3. Это количество значений которое получается при выборе конретно бренда, модели, категории, продавца.
S>Соотвественно могут быть строки только с одним полем из бренд, модель, категория, продавец, или комбинированые.
S>Бренд нужен для для оптимизации, вычисление (схлопывание) из значений модели будет более долгим.

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

select count_ from prt_search_helper h where
h.brand_id=ххх

Точно так же по другим возможным критериям поиска.

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

Теперь касательно подсчёта количества для всех комбинаций критериев.
Как лучше сделать зависит от того насколько часто добавляются новые модели/продавцы/бренды/категории, насколько точным должны быть посчитаные количества и насколько мощный сервер
Глобально подхода два:
1. пересчитывать всю таблицу PRT_SEARCH_HELPER
плюсы: проще реализовать
минусы: при каждом добавлении — тяжеловесные запросы. при частых добавлениях — не годится, если слабый сервер/большая загрузка.
2. пересчитывать только то, что собственно изменилось
плюсы: быстрее работает
минусы: сложнее реализовать

В моём приложении новые данные поступали по ночам, тогда я и пересчитывал результаты. В оракле я это делал с помощью group by rollup/cube, в постгресе к сожалению такое не поддерживается, можно сэмулировать union-ами, правда, громоздко получится.

Здравствуйте, KRA, Вы писали:

KRA>При первом показе (т.е. без каких либо наложеных пользовательских критериев) показываете для каждого, скажем, бренда результат запроса
KRA>

KRA>select count_ from prt_search_helper h where
KRA>h.brand_id=ххх
KRA>

KRA>Точно так же по другим возможным критериям поиска.

Конечно же запрос должен быть такой

 select count_ from prt_search_helper h where
 h.brand_id=ххх and
 h.model_id is null and
 h.category_id is null and 
 h.seller_id is null

Здравствуйте, KRA, Вы писали:

KRA>Здравствуйте, KRA, Вы писали:

KRA>>При первом показе (т.е. без каких либо наложеных пользовательских критериев) показываете для каждого, скажем, бренда результат запроса
KRA>>

KRA>>select count_ from prt_search_helper h where
KRA>>h.brand_id=ххх
KRA>>

KRA>>Точно так же по другим возможным критериям поиска.

KRA>Конечно же запрос должен быть такой
KRA>

KRA> select count_ from prt_search_helper h where
KRA> h.brand_id=ххх and
KRA> h.model_id is null and
KRA> h.category_id is null and 
KRA> h.seller_id is null
KRA>

мы почти подошли к исходной постановке вопроса.
1. В вашем запросе не хватает group by так как мне надо не одно значение вытащить а группу
2. В таком виде запрос работает 300 мс на сервере класса core 2 duo
3. Тенденций к уменьшению времени нет. Слишком большая таблица получается, при этом я исходную задачу сайта mobile.de
сократил на кучу измерений, то есть по факту записей должно быть еще больше! Задача точно както решается, ведь Mobile.de быстро отдает все это

Здравствуйте, Аноним, Вы писали:

А>Здравствуйте, KRA, Вы писали:

KRA>>Здравствуйте, KRA, Вы писали:

KRA>>>При первом показе (т.е. без каких либо наложеных пользовательских критериев) показываете для каждого, скажем, бренда результат запроса
KRA>>>

KRA>>>select count_ from prt_search_helper h where
KRA>>>h.brand_id=ххх
KRA>>>

KRA>>>Точно так же по другим возможным критериям поиска.

KRA>>Конечно же запрос должен быть такой
KRA>>

KRA>> select count_ from prt_search_helper h where
KRA>> h.brand_id=ххх and
KRA>> h.model_id is null and
KRA>> h.category_id is null and 
KRA>> h.seller_id is null
KRA>>

А>мы почти подошли к исходной постановке вопроса.
А>1. В вашем запросе не хватает group by так как мне надо не одно значение вытащить а группу
И вернулись к тому вопросу что я задавал к начальному посту: зачем нужен group by? Вышеприведёный запрос сразу возвращает количество моделей для бренда (именно, то что, как я понимаю, должно отображаться в скобках возле этого бренда). Зачем нужен ещё group by не ясно.

Здравствуйте, KRA, Вы писали:

KRA>Здравствуйте, Аноним, Вы писали:

А>>Здравствуйте, KRA, Вы писали:

KRA>>>Здравствуйте, KRA, Вы писали:

KRA>>>>При первом показе (т.е. без каких либо наложеных пользовательских критериев) показываете для каждого, скажем, бренда результат запроса
KRA>>>>

KRA>>>>select count_ from prt_search_helper h where
KRA>>>>h.brand_id=ххх
KRA>>>>

KRA>>>>Точно так же по другим возможным критериям поиска.

KRA>>>Конечно же запрос должен быть такой
KRA>>>

KRA>>> select count_ from prt_search_helper h where
KRA>>> h.brand_id=ххх and
KRA>>> h.model_id is null and
KRA>>> h.category_id is null and 
KRA>>> h.seller_id is null
KRA>>>

А>>мы почти подошли к исходной постановке вопроса.
А>>1. В вашем запросе не хватает group by так как мне надо не одно значение вытащить а группу
KRA>И вернулись к тому вопросу что я задавал к начальному посту: зачем нужен group by? Вышеприведёный запрос сразу возвращает количество моделей для бренда (именно, то что, как я понимаю, должно отображаться в скобках возле этого бренда). Зачем нужен ещё group by не ясно.

Чуть выше уже приводил структуру
Марка
* VW (187951)
* Mercedes-Benz (122658)
* BMW (105710)
* Opel (98532)
* Audi (97912)

вот чтобы это вывести за один запрос, а не за 5 нужен и group by