Все здравствуйте!

объем 10-100 k записей
есть два естественных поля, являющиеся ключевыми при идентификации/сравнении записей и доступе к ним



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



а также непосредственная сортировка


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

1. достаточно ли создать  составной индекс key(`a`, `b`)
1.1 правда ли, что в этом случае запрос с "условием 1" эффективней разбить на объединение (union) двух отдельных запросов с отдельными условиями , соединенными в "условии 1" через ||. Или MqSql сам эффективно и адекватно воспользуется индексом?
2. Или будет гораздо производительней создать дополнительно поле-нидекс 



с простыми дальнейшими сортировками и сравнениями 


С точки зрения сортировки, конечно, думаю разницы особой нет.
Больше интересует ситуация с || - условием. Корректно ли MySql воспользуется составным индексом в данном случае, т.к. вроде?

explain показывает, что в данном случае пользуется только индексом по полю `a`
Стоить заметить, что в данных одному значение`a` практически уникально, но возможны !редкие коллизии с небольшими вариациями по `b` в количествах думаю максимум 2-5 (самый потолок порядка 10) значений.


Т.е. в этом случае действительно оправдано игнорирование сервером второго индекса или все же 
 использовать union или сделать индекс по 'склеенному'  bigint'у будет эффективнее?

Спасибо!!!

1. Да (впрочем, в зависимости от полной структуры и точного текста запроса возможны варианты)
1.1. Да, если использовать union all, иначе нет
2. Да (впрочем, есть ли тогда смысл раздельного хранения?) 

имеется ввиду не union distinct?
я же так понимаю, условия `t1`.`a` > `t2`.`a` и `t1`.`a` = `t2`.`a` и так  абсолютно поделят множество... 
и в distinct и нет необходимости? 
или поправка именно в разрезе эффективности?

и еще уточню на всякий случай, хоть изначально предполагал для себя ответ) 
вопрос 1.1 звучал: эффективней ли разбить ИЛИ mysql гений оптимизации?
смею предположить ДА - эффективней?


Если рассматривать задачу исходя только из описанных критериев, какой подход самый оптимальный?
даже если не брать во внимание непосредственно аспект накладности  при самой возне с union и сравнивать исключительно по производительности доступа к данным

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

Спасибо!

Я не знаю, чтотакое  union distinct.

Имеется в виду определенное стандартом как не обязательное для упоминания, но действующее по умолчанию ключевое слово http://dev.mysql.com/doc/refman/5.0/en/union.html

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

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


В частном случае, похоже что - да.
Условие "> `t2`.`a`" требует обособленного индекса по `a` или же составного, где `a` был бы упомянут первым
Условие "`= `t2`.`a` && > `t2`.`b`" требует составного индекса по `a` и `b` и, при том, что по `a` требуется прямой доступ, а по `b` требуется поиск по диапазону, `b` в составном индексе должен быть упомянут последним, `a` первым.

Оба критерия дополняют друг друга, для удовлетворения обоих критериев достаточно индекса по паре `a`,`b`


Здесь надо смотреть на план в части критерия доступа, но, думаю что мася врядли cумеет объеденить результат двух разных предикатов доступа к одному индексу и запрос через union all выглядит как более надежный способ не сбить масю с понтолыку.

Однако есть один ньюанс - сортировка.
Требование сортировки заставляет оформлять запрос с union all в подзапрос и применять к результату сортировку. И тогда индекс для сортировки не сможет быть использован.

В принципе, думаю, не зазорно будет будет указать order by в каждом из подзапросов union all и надеяться на то, что объеденяться будут уже отсортированные множества. При этом, скорее всего, индекс сможет быть использован для сортировки. Думаю надежды оправдаются, но чтобы вместо надежд была уверенность, лучше бы ознакомиться с документацией по этому вопросу, гарантируется ли такое поведение. А тут я думаю, что врядли, ибо фиксация такого поведения в документации - это гвоздь в крышку гроба движка ибо для поддержки обратной совместимости придется отказаться от идеи параллелить выполнения объединяемых подзапросов в будущем.

Цена вопроса.
Мне кажется что игра не стоит свеч, и, в общем случае, усложнив запрос мы врядли выиграем достаточно спичек. Если для каждого значения `a` приблизительно одинаковое количество `b`, по критерию  && > `t2`.`b` будет отфильтрован из результата сравнительно не большой процент данных, и, я думаю, что такая оптимизация не даст сколь нибудь существенного профита против индексного доступа по предикату ">= `a`" и фильтарции по критерию "= `a` and > `b`". А для такого плана индекса по `а` - достаточно. `b` в хвост индекса можно добавить лишь для оптимизации сортировки. 

Добавлено @ 11:24

Есть же еще один, самый главный ньюанс....

Судя по тому, как вы записываете критерии отбора, у вас производится селфджойн этой таблицы. Для лидирущей таблицы производится фуллскан, для выполнения объединения используется индекс. В этом случае, переписав запрос на union all вы, наверняка, получите два фуллскана вместо одного, что, скорее всего, более чем полностью нивелирует полученный вами профит от более точного отбора по индексу при объединении. Более того, при объединени больших наборов данных, вполне может оказаться более эффективным hash join(не знаю умеет ли его мася), который, в отличии от nested loop, не сможет использовать индекс.

Это сообщение отредактировал(а) Zloxa - 13.5.2013, 11:29

именно о чем я и говорил

Добавлено через 7 минут и 49 секунд

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

если Вы касательно


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


Всем спасибо!