ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Анализ запроса DISTINCT, GROUP BY
3 сообщений из 3, страница 1 из 1
Анализ запроса DISTINCT, GROUP BY
    #39121319
devKP
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
devKP
Гость
Версия MySQL: 5.6.27

Задача: вывести список уникальных пар id с одинаковыми title.

Есть таблица(упрощенная версия):
Код: sql
1.
2.
3.
4.
5.
CREATE TABLE `items` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=201109 DEFAULT CHARSET=utf8;



Реализовал два решения и пытаюсь разобраться почему одно работает быстрее другого. Если есть другие решения так же бы на них посмотрел. Дополнительные индексы добавлять не надо.

1ый запрос:
Код: sql
1.
2.
3.
4.
5.
SELECT g.id, gs.id 
    FROM item i 
    JOIN item im ON i.title = im.title AND i.id != im.id 
    GROUP BY LEAST(i.id, im.id), GREATEST(i.id, im.id) 
    ORDER BY NULL;



EXPLAIN:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 106 | Using temporary |
| 1 | SIMPLE | im | ALL | NULL | NULL | NULL | NULL | 106 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

EXPLAIN FORMAT=JSON:
Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "i",
              "access_type": "ALL",
              "rows": 106,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "im",
              "access_type": "ALL",
              "rows": 106,
              "filtered": 100,
              "using_join_buffer": "Block Nested Loop",
              "attached_condition": "((`tmp_login`.`im`.`name` = `tmp_login`.`i`.`name`) and (`tmp_login`.`i`.`id` <> `tmp_login`.`im`.`id`))"
            }
          }
        ]
      }
    }
  }
}



2ой запрос:
Код: sql
1.
2.
3.
4.
SELECT DISTINCT LEAST(i.id, im.id) , GREATEST(i.id, im.id) 
    FROM  `items` i
    JOIN  `items` im ON i.title = im.title
    AND i.id != im.id



EXPLAIN:
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | i | ALL | NULL | NULL | NULL | NULL | 106 | Using temporary |
| 1 | SIMPLE | im | ALL | NULL | NULL | NULL | NULL | 106 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

EXPLAIN FORMAT=JSON:
Код: javascript
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
{
  "query_block": {
    "select_id": 1,
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "i",
            "access_type": "ALL",
            "rows": 106,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "im",
            "access_type": "ALL",
            "rows": 106,
            "filtered": 100,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "((`tmp_login`.`im`.`name` = `tmp_login`.`i`.`name`) and (`tmp_login`.`i`.`id` <> `tmp_login`.`im`.`id`))"
          }
        }
      ]
    }
  }
}



Теперь непосредственно к вопросу:
Как можно обьяснить, что 2ой запрос работает быстрее? Где можно почитать про расшифровку explain'a в формате json? Возможно есть более оптимальный путь решения этой задачи?

Если посмотреть на простой explain, то они одинаковые и сделать вывод почему один быстрее другого сделать сложно(во всяком случае мне). А как этот вывод сделать из формата json я пока не совсем понимаю. Интересно было бы понять, что происходит под капотом. Возможно ответ находится в описание distinct, group by, temporary table, количестве данных в этой таблице? Не понятно для меня так же почему в 2ом запросе с DISTINCT используется временная таблица( https://docs.oracle.com/cd/E17952_01/refman-5.6-en/internal-temporary-tables.html ).

Буду рад любым ответам, спасибо!
...
Рейтинг: 0 / 0
05.12.2015, 14:06:00
| Ответить | Цитировать | Написать  
Анализ запроса DISTINCT, GROUP BY
    #39121398
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
devKP
Код: sql
1.
AND i.id != im.id

Можно использовать строгое "меньше" (или "больше"). Заодно избавишься и от LEAST/GREATEST, и от DISTINCT...
...
Рейтинг: 0 / 0
05.12.2015, 17:38:48
| Ответить | Цитировать | Написать  
Анализ запроса DISTINCT, GROUP BY
    #39121441
devKP
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
devKP
Гость
Akina, Спасибо! Хороший вариант.
...
Рейтинг: 0 / 0
05.12.2015, 18:59:06
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Анализ запроса DISTINCT, GROUP BY
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39121398&tid=1832409]:
 0ms
get settings:
 11ms
get forum list:
 31ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 55ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 242ms
total:  398ms
созд. / загр.: 398ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]