Re: recursive search
From: Ikke <hviaene_at_invalid.org>
Date: Sun, 11 Sep 2016 09:18:59 +0200
Message-ID: <CaidnScaE_b5mEjKnZ2dnUU7-LPNnZ2d_at_edpnet.net>
[Quoted] >> On 09/10/2016 06:27 PM, mireero wrote:
>>
>> I would recommend you to take the step over to MariaDB and it's oqgraph,
>> the transition is quite simple, just uninstall the mySQL and install the
>> MariaDB, run a database update as you would normally do when you upgrade
>> your database. No need to change any code you already done and most
>> distributions do provide MariaDB as a replacement for mySQL.
>>
>>
>>
>> Making a recursive stored procedure is kind of the same as making a
>> recursive function, where you start with a starting point and then call
>> the same sp, but now with the next generation.
>> The cursor will work like a for-loop.
>>
>> Just keep in mind, the more generations you have, the longer time it
>> will take with mysql (or other traditional RDBMS), so if you will have
>> many generations, you need other alternatives and even running logic in
>> another layer will not be as fast as a graph-database.
>>
>> I'm not talking you to replace the whole system with a graph-database,
>> but take help of it where the mySQL ain't good enough.
>>
Oh yes, I used the "SELECT ..... START WITH ...... CONNECT BY ....." in Oracle for a multilevel kit bill of materials, worked like a charm. The problem with the OP's setup is that in his/her case, the statement would never end.
Date: Sun, 11 Sep 2016 09:18:59 +0200
Message-ID: <CaidnScaE_b5mEjKnZ2dnUU7-LPNnZ2d_at_edpnet.net>
Jerry Stuckle wrote:
> On 9/10/2016 12:57 PM, J.O. Aho wrote:
[Quoted] >> On 09/10/2016 06:27 PM, mireero wrote:
[Quoted] >>> Thanks for the answers. >>> >>> What I get is: >>> - No recursive support in mysql (and might be handled by other database >>> systems). >>> - I can manually search with n joins if not going until the end is an >>> option (not my case here). >>> >>> What I have is: >>> - My app is not written around this recursive search, it's only one >>> query among maybe 30. >>> - I'd prefer the search to be fully recursive. >>> >>> Conclusion: >>> I'll stick with mysql. >>> I'll go with PHP.
>>
>> I would recommend you to take the step over to MariaDB and it's oqgraph,
>> the transition is quite simple, just uninstall the mySQL and install the
>> MariaDB, run a database update as you would normally do when you upgrade
>> your database. No need to change any code you already done and most
>> distributions do provide MariaDB as a replacement for mySQL.
>>
>>
>>> I'll have a look at cursors (because I'm still learning and I'm >>> interested). >>> I do know basics of stored procedures but if you has hints/links about >>> how to create a recursive search, I would appreciate (I know, I can also >>> google it!).
>>
>> Making a recursive stored procedure is kind of the same as making a
>> recursive function, where you start with a starting point and then call
>> the same sp, but now with the next generation.
>> The cursor will work like a for-loop.
>>
>> Just keep in mind, the more generations you have, the longer time it
>> will take with mysql (or other traditional RDBMS), so if you will have
>> many generations, you need other alternatives and even running logic in
>> another layer will not be as fast as a graph-database.
>>
>> I'm not talking you to replace the whole system with a graph-database,
>> but take help of it where the mySQL ain't good enough.
>>
> > J.O., a graph-database is not the same as recursive SQL. Recursive SQL > has been part of the standard since sometime in the 90's, supported by > most of the commercial databases, but not MySQL (or MariaDB, AFAIK). > > I believe PostGreSQL supports recursive SQL, and the free versions of > DB2 and Oracle do, also. >
Oh yes, I used the "SELECT ..... START WITH ...... CONNECT BY ....." in Oracle for a multilevel kit bill of materials, worked like a charm. The problem with the OP's setup is that in his/her case, the statement would never end.
Herman Viaene Received on Sun Sep 11 2016 - 09:18:59 CEST