mysql improve view with subquery

Question

I need to compare rows in the same column, so I have the following mysql query which works well giving the expected result.

SELECT x.aord, 
       x.anode AS parent, 
       x.bnode AS child 
FROM   (SELECT a.ordinal               AS aord, 
               a.id_dt_graph_node_edge AS aid, 
               a.id_dt_graph_node      AS anode, 
               b.ordinal               AS bord, 
               b.id_dt_graph_node_edge AS bid, 
               b.id_dt_graph_node      AS bnode 
        FROM   dt_graph_node_edge a 
               JOIN dt_graph_node_edge b 
                 ON a.ordinal < b.ordinal) x 
       LEFT JOIN (SELECT a.ordinal               AS aord, 
                         a.id_dt_graph_node_edge AS aid, 
                         a.id_dt_graph_node      AS anode, 
                         b.ordinal               AS bord, 
                         b.id_dt_graph_node_edge AS bid, 
                         b.id_dt_graph_node      AS bnode 
                  FROM   dt_graph_node_edge a 
                         JOIN dt_graph_node_edge b 
                           ON a.ordinal < b.ordinal) y 
              ON x.aord = y.aord 
                 AND x.bord > y.bord 
WHERE  y.bord IS NULL 
ORDER  BY x.aord, 
          x.bord

I found that it's impossible to create a view on this query due to error #1349. Can anyone suggest a better way to make such query, with special focus on speed, actually this query is very slow. Thanks.

Please, post the `EXPLAIN` for this statement. (Post = update your question) — Kermit, Apr 09 '13 at 02:53

score 0 · Accepted Answer · answered Apr 09 '13 at 02:59

The reason why you cannot create a VIEW from your query is because it contains a subquery on it. VIEW, according on docs, cannot contains a subquery in the SELECT query. The workaround is to create a VIEW first on the subquery, example

CREATE VIEW firstSubquery
AS
SELECT a.ordinal               AS aord, 
       a.id_dt_graph_node_edge AS aid, 
       a.id_dt_graph_node      AS anode, 
       b.ordinal               AS bord, 
       b.id_dt_graph_node_edge AS bid, 
       b.id_dt_graph_node      AS bnode 
FROM   dt_graph_node_edge a 
       JOIN dt_graph_node_edge b 
         ON a.ordinal < b.ordinal;

CREATE VIEW secondSubquery
AS
SELECT  a.ordinal               AS aord, 
        a.id_dt_graph_node_edge AS aid, 
        a.id_dt_graph_node      AS anode, 
        b.ordinal               AS bord, 
        b.id_dt_graph_node_edge AS bid, 
        b.id_dt_graph_node      AS bnode 
FROM   dt_graph_node_edge a 
        JOIN dt_graph_node_edge b 
        ON a.ordinal < b.ordinal;

and join the newly created VIEW on the current query,

CREATE VIEW finalVIEW
AS
SELECT x.aord, 
       x.anode AS parent, 
       x.bnode AS child 
FROM   firstSubquery x 
       LEFT JOIN secondSubquery y 
              ON x.aord = y.aord AND x.bord > y.bord 
WHERE  y.bord IS NULL 
ORDER  BY x.aord,  x.bord

PS: VIEWS in MySQL are horrible

Your answers was right. Yes MySQL do not have an option to create a view with sub-queries. It is a indirect way to approach this situation. — Raja, Oct 28 '14 at 08:17

mysql improve view with subquery

1 Answers1