Как оптимизировать "тяжелый" запрос?

[Bermuda]

Как оптимизировать "тяжелый" запрос?

CREATE TABLE tags_cont (
  idcont int(10) unsigned NOT NULL auto_increment,
  modulo varchar(255) NOT NULL,
  relid int(10) unsigned NOT NULL,
  PRIMARY KEY  (idcont),
  UNIQUE KEY idcont (idcont),
  UNIQUE KEY modulo_relid (relid, modulo)
) ENGINE=InnoDB;

500 000 записей

CREATE TABLE tags_tags (
  idtag varchar(255) NOT NULL,
  relcont int(10) unsigned NOT NULL,
  PRIMARY KEY  (idtag, relcont),
  KEY idtag (idtag),
  KEY relcont (relcont),
  CONSTRAINT tags_cont_ibfk_1 FOREIGN KEY (relcont) REFERENCES tags_cont (idcont) ON DELETE CASCADE
) ENGINE=InnoDB;

5 000 000 записей

Нужно посчитать наиболее часто используемые тэги. Запрос занимает много времени, мускул начинает создавать на диске временные таблицы чудовищного размера. В реальном времени такие запросы лучше не делать. Решил сделать скрипт который делает запрос, результат сохраняет в таблиц (своеобразный кэш). Хотел повесить в крон, пусть себе выполняется каждый час. Но запрос в любом случае выполняется долго, почти четыре часа.

SELECT idtag, modulo, COUNT(idtag) as contador
FROM tags_tags
LEFT JOIN tags_cont
ON tags_tags.relcont = tags_cont.idcont
GROUP BY idtag, modulo
HAVING contador > 2
LIMIT 100

Индексы стоят. Как оптимизировать?

 

[Фанат]

а где результат EXPLAIN?

 

[Bermuda]

+----+-------------+-----------+--------+----------------+---------+---------+--------------------------+---------+----------------------------------------------+
| id | select_type | table     | type   | possible_keys  | key     | key_len | ref                      | rows    | Extra                                        |
+----+-------------+-----------+--------+----------------+---------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | tags_tags | index  | NULL           | relcont | 4       | NULL                     | 4332448 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | tags_cont | eq_ref | PRIMARY,idcont | idcont  | 4       | mvc3_3.tags_tags.relcont |       1 |                                              |
+----+-------------+-----------+--------+----------------+---------+---------+--------------------------+---------+----------------------------------------------+

 

[hermit_refined]

1. что хранится в таблице tags_tags и в каком отношении находятся idtag и relcont.
2. разве в запросе не нужна сортировка?..
3. что такое tags_cont.modulo.

 

[Bermuda]

Автор оригинала: hermit_refined
1. что хранится в таблице tags_tags и в каком отношении находятся idtag и relcont.

Начать следует с таблицы tags_cont. В ней хранится
idcont - первичный ключ
modulo - имя модуля (новости, пользователи, картинки и etc)
relid - id записи в модуле

В таблице tags_tags хранится
idtag - сам тэг
relcont - ссылка на таблицу tags_cont (FOREIGN KEY (relcont) REFERENCES tags_cont (idcont))

Таким образом строится сквозная система тэгов. Тэги из таблицы tags_tags связаны через таблицу tags_cont с записями различных модулей. Таблица tags_cont указывает какие тэги tags_tags используются для записей c relid=id в таблице modulo.

idtag, relcont==idcont, [modulo, relid]==modulo.id
^^^       ^^^^^^^^                 ^^^^^^^^^^^
тэг      foreign key               псевдосвязь

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

2. разве в запросе не нужна сортировка?..

Хотелось бы, но с ней оно вообще умирает.

В любом случае результат запроса будет сохранен в отдельной таблице "как есть" и уже к нему будут применяться выборки с сортировкой, но уже без COUNT и GROUP BY

 

[hermit_refined]

В любом случае результат запроса будет сохранен в отдельной таблице "как есть"

ну, у вас там limit стоял...

в общем, примерно так:

во-первых, тут напрашивается нормализация - заменяем имя модуля на его id (создавая таблицу всех модулей), заменяем тег на его id (аналогично).
далее есть два пути.

1. продолжая логику запроса: маленькая денормализация - меняем relcont в tags_tags на пару relid и moduloId.
если теперь tags_cont не нужна - прибиваем (тогда можно так же поступить и во втором пути).
имея таблицу с полями relid, moduloId, tagId составляем простой запрос.

2. создаем таблицу из moduloId, tagId, cnt и поддерживаем её в актуальном состоянии.

 

[Bermuda]

Автор оригинала: hermit_refined
во-первых, тут напрашивается нормализация - заменяем имя модуля на его id (создавая таблицу всех модулей), заменяем тег на его id (аналогично).

Где тут нормализация я не вижу. То что Вы предлагаете, это заменить текстовый id (имя модуля, тег) на числовой. А чем имя модуля не id? Разве id только числовые? И еще получим две таблицы. В теории реляционных баз данных не сказано, что id это обязательно число. Хотя если мускул все же заточен под числа, то может работать быстрее.

1. продолжая логику запроса: маленькая денормализация - меняем relcont в tags_tags на пару relid и moduloId.
если теперь tags_cont не нужна - прибиваем (тогда можно так же поступить и во втором пути).
теперь имея таблицу с полями relid, moduloId, tagId составляем простой запрос.

Так у меня сначала и было. Посчитал, что неэкономно. Нормализовал. Может быть вы и правы и GROUP BY и COUNT будут работать быстрее.

2. создаем таблицу из moduloId, tagId, cnt и поддерживаем её в актуальном состоянии.

Что я и пытаюсь сделать. Запроc необходимый для получения информации для поддержания вспомогательной таблицы в актуальном состоянии длится четыре часа.

-~{}~ 13.03.07 22:30:

Автор оригинала: hermit_refined
ну, у вас там limit стоял...

Лимит стоял, потому как клиент тоже не железный

-~{}~ 13.03.07 22:33:

Цель запроса -- сформировать облако тэгов.

Вот тут мне начальство подсказывает, что фиг с ним, что четыре часа занимает. Веса тэгов со временем не сильно меняются, так что можно запускать один раз в сутки - ночью. Может быть так и осталвю.

 

[zerkms]

а что если в угоду производительности в tags_tags добавить поле `count`, в котором хранить число записей с этим тегом?

 

[hermit_refined]

Где тут нормализация я не вижу.

ну, если вы про теорию нормализации как таковую, то она вообще не рассматривает ни данные в динамике, ни их избыточность в контексте хранения.

например, у вас название модуля - лишь его атрибут, пусть и уникальный, и он вполне может потребовать изменения. что будете делать в таком случае?... вот-вот.
если же это название используется только внутри приложения, и нигде не выводится... тогда это просто очень глупый выбор с точки зрения именно хранения данных (объем, переменная длина) и последующих выборок (объем временных таблиц, etc).

в общем, посмотрите в гугле про "суррогатные ключи".

Что я и пытаюсь сделать. Запроc необходимый для получения информации для поддержания вспомогательной таблицы в актуальном состоянии длится четыре часа.

нет. я имею в виду один раз создать таблицу, и далее обновлять её при добавлении/удалении всяких сущностей с тегами.

 

[Bermuda]

Автор оригинала: hermit_refined
например, у вас название модуля - лишь его атрибут, пусть и уникальный, и он вполне может потребовать изменения. что будете делать в таком случае?... вот-вот.
если же это название используется только внутри приложения, и нигде не выводится... тогда это просто очень глупый выбор с точки зрения именно хранения данных (объем, переменная длина) и последующих выборок (объем временных таблиц, etc).

Я абсолютно согласен, но в таком случае потребуется по одной таблице тэгов на модуль. В свою очередь это приведет к проблемам выборки "облака тэгов" для всего проекта в целом. Union и все вытекающие.

в общем, посмотрите в гугле про "суррогатные ключи".

Смотрел. Как-то нереляционно.

 

[hermit_refined]

но в таком случае потребуется по одной таблице тэгов на модуль.

это уже другая крайность, я её не предлагал.

Смотрел. Как-то нереляционно.

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

 

[Фанат]

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

я теориев не изучал, и постеснялся влезать с этим предложением.
но, вообще-то, мне кажется, что четыре байта заведомо меньше, чем 255.

-~{}~ 14.03.07 07:54:

Хотя это мне всё равно представляется паллиативом. как и очевидная замена варчара на чар.
что-то надо менять со структурой данных.

как я понимаю, странно выглядящая для меня группировка по двум полям из двух разных таблиц требует той самой огромной таблицы под декартово произведение 5 миллионов на 500 тыщ.
я бы, всё-таки, группировал по одному полю. точнее сказать не могу, поскольку испанского не знаю и смысла полей из названий не извлекаю

 

[hermit_refined]

что-то надо менять со структурой данных.

дык я распрямив структуру и предложил ему вместо двух одну "маленькую" и понятную таблицу из id_модуля, id_сущности_в_модуле и id_тега.

той самой огромной таблицы под декартово произведение 5 миллионов на 500 тыщ.

декартова произведения тут нет, объединение идет по внешнему ключу.
но 5 млн. раз искать по ключу в таблице из 500 тысяч - тоже не сахар.

 

[baev]

Возможно, заблуждаюсь.

Но у меня почему-то стойкая уверенность, что если в результатах EXPLAIN есть "Using filesort", то выборка заведомо тормозная.

P.S. Цитата из мануала:

With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.

Источник: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

 

[Bermuda]

Дико извиняюсь, но я забыл указать, что создание "облака тэгов" это не единственная задача, более того она второстепенная.

В реальном времени должно шустро работать
1. Добавление новых тэгов
2. Поиск контента (modulo + relid) по тэгу. Т. е. есть тэг, нужно узнать какие записи и в каких модулях его используют.
3. Поиск тэгов используемых в записи. Т. е. есть запись в модуле, нужно вывести список тэгов которые для нее используются.

Потому, что-то мне подсказывает что это

заменяем имя модуля на его id (создавая таблицу всех модулей), заменяем тег на его id (аналогично)

но, вообще-то, мне кажется, что четыре байта заведомо меньше, чем 255

потребует дополнительных JOIN-ов с таблицами "id,ИмяТэга" и "id,ИмяМодуля", что в свою очередь сильно сыграет на производительности.

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

-~{}~ 14.03.07 09:22:

baev
И как это бороть?
Хм... Там ORDER BY не используется.

ORDER BY NULL

 

[akd]

я может чего-то непонимаю, но кому надо это "облако тегов"? зачем его получать да еще и каждый день?

все остальные вещи, при правильных индексах будут работать достаточно быстро.

 

[baev]

Там ORDER BY не используется.

Где «там»?
Если MySQL пишет, что использует filesort, значит ей приходится что-то упорядочивать при выборке:

#

Using filesort

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 7.2.12, “ORDER BY Optimization”.

Цитата отсюда: http://dev.mysql.com/doc/refman/5.0/en/explain.html

 

[Bermuda]

Где «там»?

В смысле у меня . Я еще не совсем проснулся. Туплю.
ORDER BY NULL не помог :-(

кому надо это "облако тегов"?

Мне.

все остальные вещи будут работать быстро.

Так они и работают быстро.

 

[akd]

Bermuda, ну раз тебе это действительно надо, то больше добавить нечего

для внеклассного чтения: http://www.oreilly.com/catalog/sqltuning/

 

[chira]

Bermuda
судя по EXPLAIN для таблицы tags_tags используесться индекс relcont
попробуй заставить использовать индекс idtag
и в таблице tags_cont вместо idcont создай составной индекс idcont,modulo