Индексы при группировке. Подскажите чайнику.

Фанат

oncle terrible
Команда форума
Индексы при группировке Подскажите чайнику.

Мы с моим форумом тормозим.
Задача - построить список тем с количеством ответов в теме и датой последнего ответа.
и то и другое использует временную таблицу, а я не понимаю - почему.
все сообщения в одной таблице, в поле root лежит id темы.

Код:
mysql> explain SELECT root, count(id) FROM bor GROUP BY root ;
+-------+------+---------------+------+---------+------+------+-----------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+-------+------+---------------+------+---------+------+------+-----------------+
| bor   | ALL  | NULL          | NULL |    NULL | NULL |  100 | Using temporary |
+-------+------+---------------+------+---------+------+------+-----------------+

mysql> explain SELECT root, max(date) FROM bor GROUP BY root;
+-------+------+---------------+------+---------+------+------+-----------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+-------+------+---------------+------+---------+------+------+-----------------+
| bor   | ALL  | NULL          | NULL |    NULL | NULL |  100 | Using temporary |
+-------+------+---------------+------+---------+------+------+-----------------+

CREATE TABLE `bor` (
  `id` int(11) NOT NULL auto_increment,
  `date` datetime default NULL,
  `title` varchar(64) default NULL,
  `author` varchar(32) default NULL,
  `body` text,
  `root` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `root` (`root`),
  KEY `date` (`date`)
)
SELECT root FROM bor GROUP BY root ; использует индекс.
а SELECT root, id FROM bor GROUP BY root ; - уже нет
получается, что временная используется, если запросить хоть что-то, кроме группируемого поля

да, и вы будете смеяться, 3.23.57-log. но на 5.0.33 то же самое
 

findnext

Новичок
*****
нужно сделать составной индекс, без этого никак

Using temporary обычно всегла используется при GROUP BY и ORDER BY, не используется только если все значения попавшие в GROUP BY уникальные

-~{}~ 26.02.09 17:44:

делай дополнительный составной индекс ID + ROOT
 

Фанат

oncle terrible
Команда форума
еще и date
в принципе, с add index t3(root,id, date);
стало
Код:
explain SELECT root, count(id), max(date) FROM bor GROUP BY root ;
+-------+-------+---------------+------+---------+------+------+-------------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+-------+---------------+------+---------+------+------+-------------+
| bor   | index | NULL          | t3   |      18 | NULL |  100 | Using index |
+-------+-------+---------------+------+---------+------+------+-------------+
учитывая, что там еще джойн, это еще не конец...
но хотя бы с половиной запроса разобрались.

-~{}~ 26.02.09 17:49:

ахахахаха
до джойна дело не дошло.
добавили всего лишь сортировку. от слова сортир - не иначе.
Код:
explain SELECT root, count(id), max(date) dl FROM bor GROUP BY root order by dl;
+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| bor   | index | NULL          | t3   |      18 | NULL |  100 | Using index; Using temporary; Using filesort |
+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
А щас она что сортирует?

-~{}~ 26.02.09 17:51:

я, блин, свихнуся.
 

fixxxer

К.О.
Партнер клуба
есть простой способ понять, что делает mysql.

нарезать линейки, расчертить на них поля. рядом на бумажке составить индексы карандашом, и самому подвигать ;)

это можно делать и мысленно, если получится.

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

Фанат

oncle terrible
Команда форума
охохо. надо будет попробовать порисовать, если ума хватит. представить, как она все делает.
я тут пока придумал не выпендриваться, и деморализовать все нах.
в конце концов, писать эти циферки при апдейте совсем несложно

-~{}~ 26.02.09 19:42:

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

zerkms

TDD infected
Команда форума
explain SELECT root, count(id) FROM bor GROUP BY root ;
а если добавить в индекс root еще и id
делай дополнительный составной индекс ID + ROOT
извините меня за мои слова, но нахуа писать count(id) и добавлять индекс, если можно (нужно) писать count(*) ?

-~{}~ 27.02.09 09:05:

ps: ещё не понимаю, почему все резко забыли про введение избыточности?
 

Фанат

oncle terrible
Команда форума
да какая разница-то?
все равно ему приходится что-то считать. если без индекса - значит будет считать во временной таблице.
какая вообще разница, что писать в count()? она же не волшебной палочкой считает, а все теми же методами.
 

findnext

Новичок
COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause
 

zerkms

TDD infected
Команда форума
findnext
не в тему

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

-~{}~ 27.02.09 18:45:

вот и я о том же
совершенно не о том же. он выдернул невтемачную фразу про myisam, которая к вопросу не имеет ни малейшего отношения.
 

findnext

Новичок
а теперь по русски - COUNT(*) быстрее только в случае, если не выбираются никакие столбцы и не накладывается никаких условий в WHERE
 

zerkms

TDD infected
Команда форума
понятно.
людям советуешь делать "правильно", а сам добавляешь поле в индекс только для того, чтобы написать его в COUNT(), вместо идеологически правильного и более простого COUNT(*).

ну что ж - дело твоё собственно как делать, настаивать я не буду.

-~{}~ 27.02.09 18:57:

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

-~{}~ 27.02.09 18:57:

COUNT(*) быстрее только в случае
откуда ты взял слово "только" ??
 

MuXaJIbI41981

Новичок
если в запросе есть WHERE то быстрее будет работать если писать COUNT(id) нежели COUNT(*)
 

zerkms

TDD infected
Команда форума
MuXaJIbI41981
почему?

Код:
mysql> EXPLAIN SELECT COUNT(`id`), `obj_id` FROM `sys_access` GROUP BY `obj_id`;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | sys_access | ALL  | NULL          | NULL | NULL    | NULL | 3367 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*), `obj_id` FROM `sys_access` GROUP BY `obj_id`;
+----+-------------+------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | sys_access | index | NULL          | obj_id_gid | 10      | NULL | 3367 | Using index |
+----+-------------+------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
особо рьяно спорящих прошу прокомментировать эти результаты
 

MuXaJIbI41981

Новичок
zerkms
я говорил что если есть WHERE в запросе, а в ваших примерах нет и поэтому эффективнее COUNT(*)
 
Сверху