Добрый день, знатоки.
В общем, вопрос в следующем:
Есть большое дерево, которое меняется стабильно раз в месяц. Сейчас всё реализовано по модели Nested set, т.к. очень легко вытаскивать всё дерево (а делается это часто), не используя рекурсию. Но если нужно вытащить просто дерево - всё просто. Но к этому дереву JOIN'ятся данные из других таблиц, причем данных много (порядка 20-30 тысяч строк).
Для понимания вопроса, приложение делает следующее: есть структура отдела продаж (structure), типа Регион->Город->Менеджер->Торговый представитель. Т.е. дерево обычно от 5 до 7 уровней. Также есть таблица планов (plans), в которой хранятся планы продаж помесячно (structure_id, month, plan). И таблица продаж (sales) ежедневно (structure_id, date, sale).
Есть запрос, вида:
SELECT
DISTINCT s1.id as id,
s1.name as name,
s1.left_key as left_key,
s1.right_key as right_key,
s1.level as level,
(SELECT s2.id FROM structure s2
WHERE s2.left_key < s1.left_key AND s2.right_key > s1.right_key
ORDER BY s2.right_key-s1.right_key ASC LIMIT 1) as parent,
(SELECT SUM(sum) FROM structure s3
JOIN sales r ON s3.id = r.structure_id
WHERE s3.left_key >= s1.left_key AND s3.right_key <= s1.right_key
AND r.date BETWEEN $startDate AND $endDate) as sum,
p.plan as plan
FROM structure s1
LEFT JOIN plans p ON s1.id = p.structure_id
AND p.month = $month
WHERE s1.left_key >= $leftKey AND s1.right_key <= $rightKey
ORDER BY s1.left_key ASC
Запрос выполняется непростительно долго, особенно когда количество запросов к базе увеличивается (обычно это происходит утром, когда все смотрят текущее выполнение планов).
В общем, вопросов несколько
1. Стоит ли перестроить дерево в модель Adjacency list или оставить Nested set для данной задачи?
2. Как проиндексировать таблицу structure правильно (индекс, включающий left_key и right_key и два раздельных индекса не работают, а точнее mysql их не использует, и сканируется вся таблица structure).
3. Возможно, стоит исправить запрос, но я не знаю как. Если кто то знает, просьба подсказать.
Заранее спасибо.
Средний
