Как индексировать одна ко многим таблиц на sphinx?

Question

[Марат Долотов]

Есть таблица одна ко многим parent и child.

parent
- id
- name
- age

child
- id
- parent_id
- name
- age

если в конфиге source написать:

source mainSourse
{
	sql_query = \
        SELECT row_number() OVER () AS id, p.id as parent_id, p."name" as parent_name, \
        p.age as parent_age, ch.id as child_id, ch.name as child_name, ch.age as child_age\
        FROM parent p\
        JOIN child ch ON ch.parent_id=p.id\

        sql_field_string = parent_name
        sql_field_string = child_name
        sql_attr_uint = parent_age
        sql_attr_uint = child_age
}

то если у одного родителя более одного детей то в sphinx отобразить их так:

| id | parent_id | parent_name | parent_age | child_id | child_name | child_age |
|  1 |              1|                    a |                 35|            1|                aa|               2|
|  2 |              1|                    a |                 35|            2|                ab|               3|
|  3 |              1|                    a |                 35|            3|                ac|               4|
|  4 |              1|                    a |                 35|            4|                ad|               5|

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

Answer 1

[Valentyn]

Предлагаю посмотреть со стороны корректировки архитектуры для конкретного случая.
Таблицы parent и child можно объединить в одну - family, получив структуру аналогичную таблице child.
- id
- parent_id
- name
- age

Получим что-то вроде:
--------------------------------
id | parent_id | name | age
--------------------------------
1 | 0 | Отец | 30
2 | 1 | Дочь | 9
3 | 1 | Дочь2 | 7
4 | 0 | Отец2 | 41
5 | 4 | Дочь3 | 12

Тогда в самом индексе получим три поля:
- name
- age
- parent_id

Ищем среди родителей:
WHERE parent_id = 0 AND MATCH('@name "Отец"')

Ищем среди детей:
WHERE parent_id > 0 AND MATCH('@name "Дочь"')

Есть еще минимум 1 вариант избегания дублей информации - группировать по необходимому признаку и конкетировать поля с помощью MySQL функции GROUP_CONCAT использовав разделитель для дальнейшего обращения в массив.
Например, при группировке по полю parent_name нужно конкетировать 3 поля - child_id, child_name, child_age:

SELECT \
        row_number() OVER () AS id, \
        p.id as parent_id, \
        p."name" as parent_name, \
        p.age as parent_age, \
        GROUP_CONCAT(ch.id SEPARATOR '|') as child_id, \
        GROUP_CONCAT(ch.name SEPARATOR '|') as child_name, \
        GROUP_CONCAT(ch.age SEPARATOR '|') as child_age\
FROM parent p\
JOIN child ch ON ch.parent_id=p.id\
GROUP BY parent_id

При SELECT * FROM index WHERE parent_id = 1 получим что-то подобное:
----------------------------------------------------------------------------
id | parent_id | parent_name | child_id | child_name | child_age
----------------------------------------------------------------------------
1 | 1 | Отец | 1|2|3 | aa|ab|ac | 2|3|4

После выполнения запроса конкетированые строки нужно обработать для нормальной работы с child_* полями.

В целом, способ и саму необходимость каких-либо изменений хорошо определять опираясь на конкретные задачи.