Имеем следующую вьюшку:
DELIMITER $$
CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sprut_conclusion_last` AS
SELECT
MAX(`sprut_conclusion_new`.`id_result`) AS `id_result`,
`sprut_conclusion_new`.`id_sub` AS `id_sub`,
`workflow_results`.`id_document` AS `id_document`,
`workflow_schemes`.`id_stage` AS `id_stage`,
`sprut_conclusion_new`.`fio` AS `fio`
FROM ((`sprut_conclusion_new`
JOIN `workflow_results`
ON ((`sprut_conclusion_new`.`id_result` = `workflow_results`.`id_result`)))
JOIN `workflow_schemes`
ON ((`workflow_results`.`id_scheme` = `workflow_schemes`.`id_scheme`)))
GROUP BY `workflow_results`.`id_document`,`workflow_results`.`id_scheme`,`sprut_conclusion_new`.`id_sub`$$
DELIMITER ;
Прошу обратить внимание на то что
ALGORITHM=MERGEВ итоге план вьюхи, выглядит так как будто содержимое вьюхи помещается во временную таблицу, а затем уже по этому содержимому делается WHERE без всяких индексов.
EXPLAIN SELECT * FROM sprut_conclusion_last
WHERE id_document = '9208' AND id_stage = 2
А вот такой план хотелось бы получить (это план если избавиться от вьюхи)
EXPLAIN SELECT
MAX(sprut_conclusion_new.id_result) AS id_result,
sprut_conclusion_new.id_sub AS id_sub,
workflow_results.id_document AS id_document,
workflow_schemes.id_stage AS id_stage,
sprut_conclusion_new.fio AS fio
FROM sprut_conclusion_new
JOIN workflow_results ON sprut_conclusion_new.id_result = workflow_results.id_result
JOIN workflow_schemes ON workflow_results.id_scheme = workflow_schemes.id_scheme
WHERE id_document = '9208' AND id_stage = 2
GROUP BY workflow_results.id_document,workflow_results.id_scheme,sprut_conclusion_new.id_sub
Вопрос почему ALGORITHM=MERGE, работает как TEMPTABLE в моем случае?
