Как лучше организовать такую систему?

Question

[Алексей Лауд]

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

Задача:
Показывать пользователю идеи (посты), которые он еще не видел и теги которых подходят под его фильтр. Фильтр представляет из себя список тегов, часть из которых могут содержать символы, меняющие их поведение. Например, теги, которых в идеях быть не должно, теги, которые должны быть в одной идее одновременно, теги, которые обязательно должны быть в каждой идее. Теги, содержащие символ *, охватывают все подходящие под маску варианты сразу.
Идеи должны сортироваться по дате последнего перезапуска (перевод поля active из 0 в 1 поднимает идею наверх).

Сейчас сделано так:
Mysql. Таблицы ideas, ideas_tags, ideas_index и ideas_seen.
Запрос выглядит примерно так:
select * from ideas where
id in (select idea_id from ideas_tags where tag_id in (123,123,13342,232,133,82))
and id not in (select idea_id ...)
and ideas.active=1
and id not in (select idea_id from ideas_seen where user_id=182736)
order by (select id from ideas_index where idea_id=ideas.id) desc
limit 10
Иногда еще добавляются фильтры по длине идей.

Есть ли варианты того, как тот же функционал реализовать более легким для сервера способом?

Comments

[Алексей Лауд]

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

Детального решения задачи я и не ищу, мне нужно только направление, в котором искать решение.

Answer 1

[kn0ckn0ck]

Необязательно логика запроса влияет на его производительность. В большей степени на это может влиять специфика данных, физика размещения данных и т.п.

Если уверены, что проблема именно в этом запросе, то анализировать нужно не запрос, а план его выполнения (explain ...). Без плана ничего дельного сказать нельзя. Можно только предложить абстрактные идеи для оптимизации:

1. продуманные индексы
2. денормализация
3. материализованные представления
4. партиционирование
5. типы движков

Answer 2

[Boris Köln]

order by (select id from ideas_index where idea_id=ideas.id) desc

Как минимум надо убрать ideas_index из запроса, а в таблицу ideas добавить новое поле sort (денормализовать).

Answer 3

[hx510b]

Помимо предложений выше. Я бы по оптимизировал where
Условия простые и эффективные с точки зрения индексов подвинуть наверх, далее по возрастанию неэффективности.
Ведь при каждом новом условии объем выборки уменьшается, значит серверу придется делать меньше переборов
потом id in и id not in я бы свел к двум, это будет быстрее, чем несколько раз перекапывать выборку (особенно если она большая или вообще не лезет в память) для сравнения со списком. Т.е. сначала готовим списки, потом один раз сравниваем.
Конечно сам сервер должен быть оптимизирован под такие запросы, чтобы хватало выделенной памяти.