View Processing Algorithms

Asked by NT Man on 2011-05-02

Having follow view:

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 ;

Please attention to the fact that the ALGORITHM = MERGE in definition.

As a result, the plan of view looks as if the contents view placed into a temporary table, and then on the content WHERE done without any indexes.

EXPLAIN SELECT * FROM sprut_conclusion_last
   WHERE id_document = '9208' AND id_stage = 2

Click here to see plan: http://img101.imageshack.us/img101/1094/mysqlplanview.png

But such a plan would love to get (This plan get, when view is eleminated)

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

Click here to see plan: http://img694.imageshack.us/img694/8515/mysqlplanquery.png

Question: why ALGORITHM = MERGE, works as TEMPTABLE in my case?

Question information

Language:
English Edit question
Status:
Solved
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Solved by:
Sergei Golubchik
Solved:
2011-05-02
Last query:
2011-05-02
Last reply:
2011-05-02
Best Sergei Golubchik (sergii) said : #1

Quoting http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html:
...
If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

    * Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
    * DISTINCT
    * GROUP BY
 ...

NT Man (mikhail-v-gavrilov) said : #2

Thanks! Are there any immediate plans to improve the view processing algorithms for using merge with agregate functions and group by together? Otherwise my application would be very difficult to support, if instead of VIEW always would used a large SELECT.
I will be happy continue using MariaDB.

NT Man (mikhail-v-gavrilov) said : #3

Thanks Sergei, that solved my question.