Re: recursive search

On 09/10/2016 11:59 PM, Jerry Stuckle wrote: > On 9/10/2016 12:57 PM, J.O. Aho wrote:
>> On 09/10/2016 06:27 PM, mireero wrote:

>>> 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.

No one has claimed it is.

> 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).

Even if it's been around for ages, it's not optimal when looking for the kind of data that mireero asked for, as they tend to get slower and slower for the more generations you need to search for, in the same way as nested for-loops in any programming language.

For a graph database, this kind of queries are quite basic and hardly makes any difference in the time to fetch the data when having a lot of generation compared to having just one.

The question is to pick the best tool for the job.

> I believe PostGreSQL supports recursive SQL, and the free versions of > DB2 and Oracle do, also.

Sure, but then you may have to rewrite SQL queries when switching to another database engine, the benefit just switching to MariaDB is that you don't have to change anything at all and you get access to a graph-database engine which you can use for queries that would take too long with a RDBMS engine.

Sure, you get the draw back that when you start up your application, you will need to populate the graph-database with the data, as it's not stored on disk, but that can be a small trade of as you seldom do restart database/application.

-- 

 //Aho