Заметки → MariaDB 10.0 vs Mysql 5.5

Присутствовал сегодня на одном очень интересном собеседовании. Интересно оно было вопросами собеседующего — их было достаточно много и все были с заковырками. Но один из вопросов меня заинтересовал больше всего. Звучит он примерно так:

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

SELECT
	*
FROM
	`table`
WHERE
	`status` = 1 AND
	`cityId` = 55 AND
	`date_start` > "2016-04-20" AND
	`date_end` < "2016-04-31";

Я про себя ответил точно так-же, как собеседуемый — создать составной индекс, покрывающий все поля в WHERE. Какого-же было мое удивления, когда собеседующий сказал, что это не совсем правильно и в индексе не нужно поле date_end — оно не принесет пользы, только место займет. Я сильно удивился и как добрался домой — решил проверить.

Для теста я создал таблицу:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `cityId` int(10) unsigned NOT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_1` (`status`,`cityId`,`date_start`,`date_end`),
  KEY `index_2` (`status`,`cityId`,`date_start`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Закинул в эту таблицу 500к случайных значений:

for ($i = 0; $i <= 500000; $i++) {
	$start = time() + (mt_rand(0, 1000000) - 500000);
	$end = $start + mt_rand(0, 500000);
	mysqli_query($c, "
		INSERT INTO
			`test`
		SET
			`name` = 'name_$i',
			`status` = ".mt_rand(0, 1).",
			`cityId` = ".mt_rand(1, 100).",
			`date_start` = '".date("Y-m-d H:i:s", $start)."',
			`date_end` = '".date("Y-m-d H:i:s", $end)."'
		");
}

И посмотрел explain:

explain extended select
	*
from
	`test` use index(`index_1`)
where
	`status` = 1 AND
	`cityId` = 55 AND
	`date_start` > "2016-04-20" AND
	`date_end` < "2016-04-31";

Для индекса index_1, который включает в себя date_end, результат получился вот таким:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test range index_1 index_1 13 NULL 1648 100.00 Using index condition

Для index_2, без date_end:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test range index_2 index_2 13 NULL 1648 100.00 Using index condition; Using where

Т.е., собеседующий ошибся? Как так? Но тут до меня дошло — в компании используют mysql, а не mariadb! Снова проверяем, но уже на mysql 5.5.45.

Для index_1:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test range index_1 index_1 13 NULL 1648 75.00 Using where

Для index_2:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test range index_2 index_2 13 NULL 1648 100.00 Using where

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

P.S.: кстати, оптимизатор mariadb сам выберет именно index_1, а оптимизатор mysql — index_2. Не понимаю, почему люди до сих пор используют mysql, а не mariadb :/