Немного вопросов о условиях?

Question

[Cyapa]

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

1. ON vs WHERE
Вопрос: Что предпочтительнее и быстрее работает, множественные связи в JOIN или условия в WHERE?
Пример:

-- Вариант первый
SELECT 
	`table_one`.`field_id`, 
	`table_two`.`field_data`
FROM 
	`table_one` INNER JOIN `table_two`
ON 
	`table_one`.`field_one` = `table_two`.`field_one` AND
	`table_one`.`field_two` BETWEEN 23 AND 32 AND
	`table_two`.`field_two` LIKE '%foobar%'

-- Вариант второй	
SELECT 
	`table_one`.`field_id`, 
	`table_two`.`field_data`
FROM 
	`table_one` INNER JOIN `table_two`
ON 
	`table_one`.`field_one` = `table_two`.`field_one`
WHERE
	`table_one`.`field_two` BETWEEN 23 AND 32 AND
	`table_two`.`field_two` LIKE '%foobar%'

Мои догадки: Немного понасиловав мозг движок google, наткнулся на рассуждения о том, что связи в секции ON применяются в процессе формирования dataset'a, а условия из WHERE фильтруют уже сформированый dataset. Получается, что разницы никакой нет? Или я ошибся с оценкой сложности? К тому же, говорят, что оптимизатор иногда сам перемещает условия между секциями на свое усмотрение.

2. Порядок
Вопрос: Влияет ли порядок конкатенации условий на производительность?
Пример:

-- Вариант первый
SELECT *
FROM `table`
WHERE
	`field_string` LIKE '%foobar%' AND
	`field_integer` BETWEEN 23 AND 32

-- Вариант второй
SELECT *
FROM `table`
WHERE
	`field_integer` BETWEEN 23 AND 32 AND
	`field_string` LIKE '%foobar%'

Мои догадки: Условия LIKE довольно затратно. Во всяком случае, затратнее (более затратно?), чем сравнение чисел. Поэтому, логично было бы предположить, что его стоит делать после того, как из dataset'a будут исключены поля, не подходящие под условие с числами. Так ли это? И если это так, делает ли работу по упорядочиванию условий оптимизатор?

3. WHERE 1
Вопрос: Влияет ли на производительность пустое условие WHERE 1 или WHERE 1=1?
Пример:

-- Вариант первый
SELECT *
FROM `table`
WHERE
	condition1 AND
	condition2 AND
	...
	conditionN

-- Вариант второй
SELECT *
FROM `table`
WHERE 1
	AND condition1
	AND condition2
	...
	AND conditionN

Мои догадки: Логично, что раз это условие, то она будет работать так же, как и остальные: применяться к каждому полю из dataset`а. Если это так, то выходит, что на больших наборах данных мы будем иметь огромное количество пустых проверок? Или, это как по другому работает? А может оптимизатор просто уберет это условие?


Я особо помешан на форматировании, можно сказать, у меня на этом пунктик, поэтому, мне важно оформлять код максимально псевдоэстетично. Недавно открыл для себя эту «фишку» — начинать каждое условие с AND/OR. Мне кажется, довольно красиво выходит. Но если это будет во вред производительности, то мне придется отказаться от этого.

Answer 1

[Melkij]

1. ON vs WHERE
Разницы не замечал (но возможно она от версии к версии есть). Оптимизатор свободно перемещает порядок джойнов, условия.

2. Порядок
Оптимизатор всё равно перепишет, приняв во внимание индексы, джойны, having и ещё чего-нибудь.

3. WHERE 1
не влияет.

Можете посмотреть, как оптимизатор переписал запрос выполнив:

explain extended запрос;
show warnings;

А так же, может быть интересно, что оптимизатор делает полезного с where: dev.mysql.com/doc/refman/5.1/en/where-optimizations.html

Comments

[Cyapa]

1. И все таки, вопрос остается открытый. Было бы интересно узнать это наверняка.

2. Эх, куда не плюнь, все за тебя делают… Значит, это все таки влияет?

3. Хм, благодарю. Я подозревал, что оптимизатор занимается подобным :)

[zednight]

1) я же вроде написал выше наверняка… Или вы выбрали один ответ и только его ветку мониторите?

Answer 2

[zednight]

1 Вспомогательные условия помещайте в where, это увеличивает производительность
2 Порядок не принципиален, и если и влияет на производительность, то не на том уровне, чтоб из-за этого переживать
3 Опять же влияние не велико, особенно при сравнении констант, но если условие излишне, его лучше исключить

Comments

[Cyapa]

Ой, прошу прощения, не заметил ваш комментарий.

1. Можно поподробнее, почему так?
2. Как выяснилось. Порядок значителен и влияет на производительность, просто оптимизатор выбирает порядок за нас.
3. Опять же, оптимизатор позволяет использовать такие конструкции.

[zednight]

1) Я это выяснял очень давно, поэтому линк не найду, попробую суть своими словами:
В момент join по cути просходит перемножение строк которое фактически проверяет условие для каждого пересечения т.е.
количествоСтрокПервойТаблицы*количествоСтрокВторойТаблицы, и если в join добавлять условия не имеющие отношения к соединению, то вы их выполняете при каждой проверке, т.е. неоправданно много раз. А вот where это простая фильтрация, которая выполняется для результирующего(или исходного, тут не помню) количества строк, т.е. значительно меньше раз. Таким образом в правильно составленном запросе, все условия фильтрации должны быть в where и having.
2) Я не разбирался на уровне оптимизатора, просто много раз писал запросы, которые выполнялись очень долго и приходилось их оптимизировать, так вот ни разу перестановка условий не повлияла значительно на время выполнения.
3) Аналогично первому.