Как избавиться от "Using temporary" при запросе с COUNT() и GROUP BY. Оптимизация.

[Bermuda]

Как избавиться от "Using temporary" при запросе с COUNT() и GROUP BY. Оптимизация.

Есть пользователи

CREATE TABLE users (
  id int(10) NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM;

Есть группы пользователей

CREATE TABLE groups (
  id int(10) NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
) ENGINE=MyISAM;

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

CREATE TABLE groups_users (
  group_id int(10) NOT NULL,
  user_id int(10) NOT NULL,
  UNIQUE KEY group_user (group_id, user_id),
  KEY group_id (group_id),
  KEY user_id (user_id)
) ENGINE=MyISAM;

Хочу вывести список только тех групп в которых находится определенный пользователь. Также я хочу знать сколько в каждой группе есть пользователей.
Делаю вот так

SELECT
    groups.id,
    groups.name,
    COUNT(members.user_id)
FROM groups_users
LEFT JOIN groups ON groups_users.group_id = groups.id
LEFT JOIN groups_users as members ON groups.id = members.group_id
WHERE groups_users.user_id = '1'
GROUP BY groups.id
ORDER BY NULL

Explain говорит, что "Using temporary"

+--+-----------+------------+------+-------------------+----------+-------+----------------------------+----+---------------+
|id|select_type| table      | type | possible_keys     | key      |key_len| ref                        |rows| Extra         |
+--+-----------+------------+------+-------------------+----------+-------+----------------------------+----+---------------+
|1 | SIMPLE    |groups_users|ref   |user_id            |user_id   | 4     |const                       |  4 |Using temporary|
|1 | SIMPLE    |groups      |eq_ref|PRIMARY,id         |PRIMARY   | 4     |mvc3_3.groups_users.group_id|  1 |               |
|1 | SIMPLE    |members     |ref   |group_user,group_id|group_user| 4     |mvc3_3.groups.id            |  1 |Using index    |
+--+-----------+------------+------+-------------------+----------+-------+----------------------------+----+---------------+

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

Как оптимизировать запрос?

 

[Sad Spirit]

Автор оригинала: Bermuda

Как оптимизировать запрос?

для начала выкинуть нах LEFT JOIN'ы, они здесь не нужны.

 

[Bermuda]

Автор оригинала: Sad Spirit
для начала выкинуть нах LEFT JOIN'ы, они здесь не нужны.

Выкинул

SELECT 
    groups.id, 
    groups.name, 
    COUNT(members.user_id) 
FROM groups_users, groups, groups_users as members
WHERE groups_users.user_id = '1' 
AND groups_users.group_id = groups.id
AND members.group_id = groups.id 
GROUP BY groups.id 
ORDER BY NULL

Все равно Explain говорит, что "Using temporary".

 

[Sad Spirit]

Автор оригинала: Bermuda
Все равно Explain говорит, что "Using temporary".

Мысклёвый мануал говорит:

Using temporary

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

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

 

[Bermuda]

Мысклёвый мануал говорит:

Уже в курсе

explain в мыскле крайне убог и понять, из каких данных и где он строит эту временную таблицу абсолютно нереально

Что делать и кто виноват?

-~{}~ 26.03.07 13:56:

Если убрать ORDER BY NULL или сделать ORDER BY groups.id, то он еще и filesort делает

 

[kostya.sys]

во первых COUNT(*) быстрее чем COUNT(members.user_id)

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

 

[Wicked]

во первых COUNT(*) быстрее чем COUNT(members.user_id)

ага, в случае когда нету where

-~{}~ 27.03.07 09:51:

[sql]CREATE TABLE `groups_users` (
`group_id` int(10) NOT NULL,
`user_id` int(10) NOT NULL,
UNIQUE KEY `group_user` (`group_id`,`user_id`),
KEY `user_group` (`user_id`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;[/sql]

[sql]SELECT
groups.id,
groups.name,
COUNT(members.user_id)
FROM groups_users
INNER JOIN groups ON groups_users.group_id = groups.id
INNER JOIN groups_users as members ON groups_users.group_id = members.group_id
WHERE groups_users.user_id = 4
GROUP BY groups_users.user_id, groups_users.group_id
ORDER BY NULL[/sql]

-~{}~ 27.03.07 09:54:

+----+-------------+--------------+--------+-----------------------+------------+---------+----------------------------+------+--------------------------+
| id | select_type | table        | type   | possible_keys         | key        | key_len | ref                        | rows | Extra                    |
+----+-------------+--------------+--------+-----------------------+------------+---------+----------------------------+------+--------------------------+
|  1 | SIMPLE      | groups_users | ref    | group_user,user_group | user_group | 4       | const                      |    5 | Using where; Using index |
|  1 | SIMPLE      | members      | ref    | group_user            | group_user | 4       | test.groups_users.group_id |    2 | Using index              |
|  1 | SIMPLE      | groups       | eq_ref | PRIMARY,id            | PRIMARY    | 4       | test.members.group_id      |    1 | Using where              |
+----+-------------+--------------+--------+-----------------------+------------+---------+----------------------------+------+--------------------------+

-~{}~ 27.03.07 10:25:

PS: а зачем нужны были следующие индексы?
1) groups.id - там вполне хватает PK.
2) groups_users.group_id - он есть leftmost prefix от group_user. Да, он мог бы быть быстрее более тяжеловесного индекса group_user на задачах, когда нужно только поле group_id, но для таблицы связей "многие ко многим" такие запросы - редкость. Вообще, у меня почти за правило создавать двунаправленные индексы в таких таблицах (using index = благо).

 

[Bermuda]

Вообще, у меня почти за правило создавать двунаправленные индексы в таких таблицах (using index = благо).

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

-~{}~ 27.03.07 09:31:

PS: а зачем нужны были следующие индексы?
2) groups_users.group_id - он есть leftmost prefix от group_user.

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

 

[Wicked]

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

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

Я таких умных составителей запросов еще не видел А в случае составного ключа (a,b,c) его тогда уж можо воспринимать как 3 независимых ключа: (a), (a,b), (a,b,c).

 

[Bermuda]

Я таких умных составителей запросов еще не видел

http://qcodo.com/

-~{}~ 27.03.07 11:11:

А семантика, в данном случае, мало того, что мне вообще не ясна

Есть поля по которым делают выборку (WHERE, GROUP BY, JOIN и etc), ровно как есть поля по которым выборка никогда не делается. Индексы мало того что ускорят выборку, также помогают понять модель данных. Понимая модель данных можно создать методы для основных операций над ней.
В случае составного индекса я могу быть уверен лишь в том, что выборка может приозводится по этому индексу, но я ничего не знаю о том, будет ли проводится выборка по одному полю, двум или всем трем. Указание всех индексыв явно, дает информацию о возможных выборках. Если приплести сюда еще и foreign key, то это дает еще информацию о отношениях. Вот что я понимаю под семантическим смыслом модели данных. Понимать семантический смысл модели данных бывает полезно при повторном использовании некоторых наработок в новых проектах учитывая большой коллектив разработчиков и тот факт, что разработку могут вести разные люди. Своего рода протокол/соглашение

 

[Wicked]

ну хорошо, твоя точка зрения мне понятна.

просто я, видимо, более трепетно отношусь к оптимизации, и стараюсь не привносить в проект таких явных излишеств. Если бы мне понадобилось описать возможные выборки, я бы воспользовался другими средствами. Например, COMMENT'ом таблицы. Хотя мне даже такого обычно не требуется.

 

[Bermuda]

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

Авторы qcodo об этом также задумались и ввели псевдо "foreign keys" которые можно указать в отдельном конфигурационном файле (на случай если база не поддерживает foreign key). В любом случае всегда приходится чем-то жертвовать. Производительностью ради масштабирования, например. Хотя любым жертвам есть предел. Может быть я слегка проиграю на инсертах из-за "лишних" индексов, но выиграю на времени разработки заказа, потому как кодогенератор мне нагенерит большую часть кода проекта. Как в том анекдоте:"Объявление: выполним ваш заказ Быстро, Качественно, Недорого. Вы можете выбрать любые два пункта" Нужно искать баланс, крайности -- оно всегда плохо.

В любом случае спасибо за решение, действительно помогло!

-~{}~ 27.03.07 11:35:

Да и вообще, 2007-й год на дворе, а мы все еще запросы к базе руками пишем.