Dwrда я уже проверил и перепроверил несколько раз, все как по учебнику. А еще выбирается только первый unique_field, второй и последующие не выбираются. Может быть, проблема в одинаковых id?
Неправильный учебник. Проблема в том, что вы неправильно делаете группировку. Подробнее см http://sqlinfo.ru/forum/viewtopic.php?id=6240

Окончательный запрос нужен вида
.. from info join (подзапрос с группировкой) ..

AkinaretvizanОкончательный запрос нужен вида
.. from info join (подзапрос с группировкой) ..А вот это уже с потолка взято.
Почему?
Джойнить, а потом проводить группировку по текстовому полю первой таблицы - плохое решение с т.з. производительности.

AkinaНу например потому, что при объёмном результате подзапроса он будет закэшен на диск.Не понял смысла фразы. Речь о том, что размер временной таблицы столь велик, что не поместится в памяти?

AkinaИли потому, что прощай использование индекса. Вернее, и.Здесь и так связь таблиц не по индексу.
А если был бы индекс, то к данным подзапроса по индексу клеим данные из первой таблицы.
Кроме того в случае индекса группировка в подзапросе хорошо на него ляжет.

AkinaВ общем, оверхед запросто может стать пухлее профита. А на реальной базе указанного назначения - гарантированно станет.Прошу пояснений.

AkinaКроме того, если поле группировки есть уникальное поле левой таблицы - то чхать с высокой колокольни на неправильность запроса с точки зрения классического сиквела.

Кстати, с какого потолка взято, что группить он собрался по тексту?Согласен, что группировать по всем полям здесь не нужно. Но все равно в результате джойна получиться большая временная таблица за счет повторяющегося текстового поля и группировка по ней всегда файлсорт, даже при наличии индекса на `unique_field`.

.. info join transactions .. group by .. order by -- вариант 1
.. info join (select ..transactions .. group by .. order by) -- вариант 2

AkinaretvizanРечь о том, что размер временной таблицы столь велик, что не поместится в памяти?
Даже если она поместится - не факт что сервер не сольёт её на диск. А поскольку это лог, причём скорее всего пухлый - такой исход более чем вероятен имхо. А если к каждой строке этого лога добавить до группировки `some_data` text из первой таблицы (вариант 1), то ситуация значительно ухудшится.


AkinaНе знаю досконально начинки сервера и того, что именно он выберет для выполнения запроса, но группировка-то идёт по моему разумеению всё-таки не по тексту. ТС говорит про какой-то "число-буквенный код", но в структурах модели его нет,Почему нет? `unique_field` tinytext,

В первом варианте у нас сначала выполняется join. На выход огромная временная таблица за счет повторения `some_data` text. Группировка при этом файлсорт даже, если есть индекс на `unique_field` (будь он хоть primary). А потом ещё и сортировка файлсорт на длинных строках за счет `some_data`.

Во втором варианте: группировка по индексу; сортировка файлсорт, но строки короче за счет отсутствия `some_data` text (правда нужен straight join). Связь же подзапроса с первой таблицей по индексу.

Мой вопрос про overhead и profit в том, что я не вижу за счет чего вариант 2 может быть хуже в отличие от обратного.

AkinaНу во-первых, вариант 2 будет немного не таким, он будет

.. info join (select ..transactions .. group by ..) order by...

В случае сортировки в подзапросе она просто будет сервером проигнорирована.
Почему проигнорирована? Можно ссылку?

Для оптимизации сортировки вар2 нужно переписать в виде

.. (select ..transactions .. group by .. order by..) straight_join info ..
тогда сортировка по сумме будет в подзапросе, а join добавит текстовые данные из первой таблицы к уже отсортированному результату.


Но сортировка вопрос вторичный, это незначительное улучшение. Основная мысль была в другом.
MySQL выполняет запрос последовательно: join, where, group by, having, order by, limit.
После того как мы сделали
.. info i join transactions t..
у нас получилась избыточная временная таблица без каких-либо индексов ( `unique_field` tinytext, `some_data` text, `value` int,). И уже по ней будет идти группировка. И никакие индексы в исходных таблицах исправить ситуацию не смогут. Собственно подзапрос и нужен, чтобы группировка шла по индексу.

А что такое БСК?

AkinaПод "сортировка учитывается" мы понимаем "ретранслируется в результат", так?Да.

AkinaПро то, что это не файловая БД, уже сказали.Но у нас частный случай. Временная таблица, индексов нет, только что над ней был совершен файлсорт (как раз за счет сортировки по сумме). Чтение из этой таблицы будет идти в нужном нам порядке. Разве нет?

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

Собственно, это можно проверить экспериментально. Вариант
.. (select ..transactions .. group by .. order by..) straight_join info ..
всегда даст результат с правильной сортировкой как не извращайся над исходными данными (хоть PK переопределяй). Это является док-вом?

Cygapb-007Если распараллелить запрос (для увеличения скорости обработки, конечно же!), то без явного указания ORDER BY в конце запроса SQL будет выполнять его так, как удобнее ему, а именно включать в итог порции данных в порядке завершения обработки на разных процессорах. В этом случае даже наличие кластерного индекса не будет определять порядок следования строк в итоге.MySQL не умеет распараллеливать запрос.

AkinaretvizanРезультат будет зависеть от порядка склейки таблиц в джойне. И для гарантии незыблемости результата нужно этот порядок явно указать оптимизатору с помощью straight_join.
Мне категорически не нравится утверждение что результат недетерминирован и зависит от порядка связывания.Это было бы так, если полагаться на волю случая, например, что оптимизатор выберет нужный порядок следования, потому что это более выгодный вариант - последовательно читать результат временной таблицы и по индексу подклеивать нужные поля из первой таблицы. Тогда да - сегодня работает правильно, а завтра звезды иначе расположились. Но в моем примере речь идет о задокументированном операторе, который и определяет порядок связывания ,т.е. результат однозначно определен пока сам запрос не перепишем.

AkinaretvizanMySQL выполняет запрос последовательно: join, where, group by, having, order by, limit.Ты не поверишь, но это НЕ ТАК. Вот если полностью заблокировать оптимизатор - то да, но кому такой сервер будет нужен?Действительно, не верю:) Мы говорим о MySQL?
Разбирая запрос, по сути оптимизатор применяет ряд правил вида: если запрос имеет вид такой, то заменить так. Потом идет последовательное выполнение - join, where, group ...

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

AkinaНет. STRAIGHT_JOIN определяет, что к чему прикручивать. Но вот как именно считывать отдельно взятую таблицу или индекс, модификатор не говорит. И сохранять следование записей в соответствии с каким-либо индексом - тоже не говорит. Это всё определит сервер на стадии выполнения. И если ему, например, не хватит оперативки - он первую половину уже отобранных записей сольёт в кэш, и продолжит выборку. А когда закончит - не станет сливать вторую, чтобы сперва вычитать и отдать клиенту первую, а после опять перечитать и отдать вторую. Если нет Order By (в т.ч. неявного, от группировки) - ему по барабану наличие каких-то там индексов, которые с конечными данными уже никак не связаны. Как быстрее, так и будет.Убедили. Спасибо за терпение.

AkinaretvizanДействительно, не верю:) Мы говорим о MySQL?
Разбирая запрос, по сути оптимизатор применяет ряд правил вида: если запрос имеет вид такой, то заменить так. Потом идет последовательное выполнение - join, where, group ...
Да, мы говорим о MySQL.
Действительно, оптимизатор по сути есть детерминированный конечный автомат с грудой правил. Вот только входных данных у него гораздо больше, чем голый текст запроса и DDL используемых таблиц. Статистика данных и доступные ресурсы сервера влияют на конечное состояние зачастую гораздо сильнее DML-текста. А ты это влияние безусловно отбрасываешь в сторону.Но это влияние другого рода: какой индекс использовать или выбрать фулскан, временная таблица в памяти или на диске и т.д.
Мое утверждение в следующем - в варианте 1
.. info join transactions .. group by .. order by ..
join всегда выполняется первым.
И какие параметры даже теоретически могут изменить этот порядок я представить не могу. Можно пояснение?

Вариант 1.
.. info join transactions .. group by .. order by

Сначала идет джойн по индексу
.. info i join transactions t..
получится избыточная временная таблица без каких-либо индексов (`unique_field` tinytext, `value` int, `some_data` text). Т.е. к каждой строке лога транзакций мы добавляем текстовое поле из первой таблицы. Получившаяся временная таблица больше исходной таблицы транзакций.
Далее группировка будет идти по этой большой временной таблице без каких-либо индексов, т.е. всегда файлсорт.
Потом ещё один файлсорт за счет сортировки по сумме.


Вариант 2.
.. info join (select ..transactions .. group by .. ) .. order by

Сначала выполняется подзапрос. Есть возможность сделать группировку по индексу и кроме того получившаяся временная таблица будет меньше исходной таблицы транзакций (меньше вероятность, что уйдет на диск в отличии от первого варианта).
Далее джойн по индексу - последовательно читается временная таблица и для каждой строки из неё по уникальному индексу ищется соответствие в первой таблице.
Получается вторая временная таблица и далее файлсорт за счет сортировки по сумме аналогичный первому варианту.


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

Arhat109По крайней мере, мои изыски с переменными показывали, что присваивание переменной в блоке where часто приводит к двойному, тройному присваиванию, по числу джойнов и проверок. То есть конечная обработка (группировка) строки результата идет сразу, а не сначала созхдается какая-то гигантская обощенная времянка.Тоже сталкивался с такой проблемой в более простом примере где был всего лишь джойн 2ух таблиц без группировки, там фишка в том не ясно какие условия и в каком порядке применяются к той или иной таблице. В итоге результат зависел от наличия индексов и самих данных.
Если есть желание поковыряться в примере, то вот

Arhat109Странно, а мне всегда казалось, что порядок такой но "построчный", то есть:

1. приклеиваем к каждой транзакции поле(я) из лога, и тут же проверяем на условия, группировки и ведем подсчет агрегата. Результат накапливается гораздо медленнее чем как-бы полная времянка. Строго по результату группировки.Имею уверенность, что это не так, но на хлеб её, конечно, не намажешь. Возможно, это следствие глубокого заблуждения.
Кто-нибудь знает истину?

Arhat109retvizan,

Странно, а мне всегда казалось, что порядок такой но "построчный", то есть:

1. приклеиваем к каждой транзакции поле(я) из лога, и тут же проверяем на условия, группировки и ведем подсчет агрегата. Результат накапливается гораздо медленнее чем как-бы полная времянка. Строго по результату группировки.Посмотрел внимательно на эксплейн, поэкспериментировал с данными и должен признать - вы правы.