performance - mysql improve view with subquery -

-

i need compare rows in same column, have following mysql query works giving expected result. 

select x.aord,         x.anode parent,         x.bnode child    (select a.ordinal               aord,                 a.id_dt_graph_node_edge aid,                 a.id_dt_graph_node      anode,                 b.ordinal               bord,                 b.id_dt_graph_node_edge bid,                 b.id_dt_graph_node      bnode            dt_graph_node_edge                 join dt_graph_node_edge b                   on a.ordinal < b.ordinal) x         left join (select a.ordinal               aord,                           a.id_dt_graph_node_edge aid,                           a.id_dt_graph_node      anode,                           b.ordinal               bord,                           b.id_dt_graph_node_edge bid,                           b.id_dt_graph_node      bnode                      dt_graph_node_edge                           join dt_graph_node_edge b                             on a.ordinal < b.ordinal) y                on x.aord = y.aord                   , x.bord > y.bord   y.bord null  order  x.aord,            x.bord

i found it's impossible create view on query due error #1349. can suggest better way make such query, special focus on speed, query slow. thanks.

the reason why cannot create view query because contains subquery on it. view, according on docs, cannot contains subquery in select query. workaround create view first on subquery, example

create view firstsubquery select a.ordinal               aord,         a.id_dt_graph_node_edge aid,         a.id_dt_graph_node      anode,         b.ordinal               bord,         b.id_dt_graph_node_edge bid,         b.id_dt_graph_node      bnode    dt_graph_node_edge         join dt_graph_node_edge b           on a.ordinal < b.ordinal;  create view secondsubquery select  a.ordinal               aord,          a.id_dt_graph_node_edge aid,          a.id_dt_graph_node      anode,          b.ordinal               bord,          b.id_dt_graph_node_edge bid,          b.id_dt_graph_node      bnode    dt_graph_node_edge          join dt_graph_node_edge b          on a.ordinal < b.ordinal;

and join newly created view on current query,

create view finalview select x.aord,         x.anode parent,         x.bnode child    firstsubquery x         left join secondsubquery y                on x.aord = y.aord , x.bord > y.bord   y.bord null  order  x.aord,  x.bord

ps: views in mysql horrible


Comments

Post a Comment

Popular posts from this blog

javascript - Image onload event not firing in firefox -

-
i'm creating image preloader. works fine in browsers except firefox (testing firefox 10.0). input array of images called image_list . these images appended dynamically body of document , load callback attached, calls function update_progress . code follows: $(image_list).each(function() { var x = $('<img />') .load(function() {update_progress(percent_loaded += step);}) .attr('src', this) .appendto('body') .css('display', 'none'); .each(function() { if(this.complete) update_progress(percent_loaded += step); }); }); in firefox, load callback , update_progress never called. yet exact same code works fine in chrome , other browsers i've tested. there way detect when image has loaded in firefox? i first check see whether image has height, checking height property. if (the_image.height > 0) { // image loaded (possibly cached) } if have cached ...
Read more

android - getbluetoothservice() called with no bluetoothmanagercallback -

-
i getting getbluetoothservice() called no bluetoothmanagercallback error in android application. i have no idea causing or bluetooth manager callbacks. can give me idea of causing problem or start looking. by reading android source code, seems warning cannot about. source code shows if call bluetoothsocket#connect(); then call bluetoothadapter.getdefaultadapter().getbluetoothservice(null); the key here, null parameter passes in above line. due this, there no callback, , bluetoothsocket class throw out warning. since warning, not think need it. https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/bluetooth/bluetoothsocket.java line 306 https://android.googlesource.com/platform/frameworks/base/+/master/core/java/android/bluetooth/bluetoothadapter.java line 1610
Read more

sql - ASP.NET SqlDataSource, like on SelectCommand -

-
i'm working on asp.net. have sqldatasource query hardcoded on selectcommand: <asp:sqldatasource id="datasource1" runat="server" cancelselectonnullparameter="false" connectionstring="<%$ connectionstrings:s.properties.settings.connectionstring %>" selectcommand="select * [table] ([col1] case @col1_param when null col1 else @col1_param end) , ([col2] case @col2_param when null col2 else @col2_param end)" selectcommandtype="text"> <selectparameters> <asp:controlparameter controlid="textbox1" name="col1_param" propertyname="text" type="string" /> <asp:controlparameter controlid="textbox2" name="col2_param" propertyname="text" type="string" /> </selectparameters> what want if enter data on 1 textbox only, data display according textbox...
Read more