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

Question

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

CREATE TABLE `endu_results` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `base_name` varchar(200) NOT NULL,
 `base_nr` int(11) DEFAULT NULL,
 `base_yob` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `endu_results_206a6355` (`base_name`),
 KEY `endu_results_63df4402` (`base_nr`),
 KEY `base_yob` (`base_yob`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3424028 DEFAULT CHARSET=utf8;enter code here

и 2-й:

CREATE TABLE `endu_resultinterest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `result_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `endu_resultinterest_3b529087` (`result_id`),
  CONSTRAINT `result_id_refs_id_19e24435` FOREIGN KEY (`result_id`) REFERENCES `endu_results` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48590 DEFAULT CHARSET=utf8;

В endu_resultstable содержится около 2 миллионов записей и меньше, чем 100K. У меня медленный запрос:

explain select base_yob from endu_resultinterest 
inner join endu_results 
on (endu_results.id = endu_resultinterest.result_id) 
order by endu_results.base_yob;

1   SIMPLE  endu_resultinterest index   endu_resultinterest_3b529087    endu_resultinterest_3b529087    4   NULL    47559   Using index; Using temporary; Using filesort

Вопрос в том, почему mysql использует этот индекс: endu_resultinterest_3b529087 - но он должен использовать base_yob - именно здесь запрашивается сортировка?

Чтобы проверить это дальше, я вручную создал 2 дополнительные идентичные таблицы endu_testresults и endu_testresultintrest и заполнил их некоторыми записями:

CREATE TABLE `endu_testresults` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `base_yob` int(11) DEFAULT NULL,
 `base_name` varchar(200) NOT NULL,
 `base_nr` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
KEY `endu_testresults_a65b2616` (`base_yob`),
KEY `endu_testresults_ba0ab39c` (`base_name`),
KEY `endu_testresults_d75ba04d` (`base_nr`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

Итак, я иду снова для объяснения:

explain select base_yob from endu_testresultinterest 
inner join endu_testresults 
on (endu_testresults.id = endu_testresultinterest.result_id) 
order by endu_testresults.base_yob;

и сюрприз сюрприз

 1  SIMPLE  endu_testresults    index   PRIMARY    endu_testresults_a65b2616    5   NULL    19  Using index

Столбец сортировки индекса base_yob (endu_testresults_a65b2616) теперь используется.

Почему в одном случае используется индекс, а в другом - использование файловой сортировки, временное использование? Размер имеет значение? Я постараюсь копировать записи из одной в другую, но не получаю это с индексами. MySql 5.6.16

Answer 1

Краткий ответ: потому что это быстрее.

Длинный ответ...

Ваши ОБЪЯСНЕНИЯ кажутся неполными - я ожидал бы 2 строки в каждой.

Первый стол в 20 (70?) Раз больше, чем второй. Для начала оптимизатор выбрал меньшую таблицу. Следовательно, изначально выполняется 1/20 объема работы. Вид, который приходит позже (ORDER BY ...) гораздо меньше работы, чем если бы для начала пришлось выполнить в 20 раз больше работы.

Выход только 48K строк, правильно? И это сколько строк во 2-й таблице, верно?

Ваш test у таблиц не было такого же большего / меньшего соотношения, не так ли? Отсюда и разные EXPLAIN,