Good afternoon everyone!
I have a question, and I would like to hear your comments.
I'm building a system in php + laravel .
I have relationships between tables, for example: Users->Roles .
It is assumed that there are 10,000 users registered with
Role == Admin. If I exclude Role Admin , the system theoretically would have to delete 10,000 rows from therole_userstable because Role Admin will cease to exist.
Would that generate 10,000 queries? Would you overcharge DB?
Hugs
In any relational system, if you have a relationship that indicates that the deletion of the parent element removes the child elements in a cascade, deleting a single parent element and all its children will occur in a single query.
Whether or not this will overload the bank depends on the cascade removal flow and the size of the mass of data to be erased. Your user records may have other relationships with your own children, which can also be casually removed, for example. But in general, removing is fast. Especially if the relationship between the elements is indexed in the column on the "child" side of the relationship.
The entire weight is in the processing of the database. For the application, a single query on a single connection is executed.
It will generate sim querys to delete and will generate extra work to raise all the places that should be deleted together. But this on cascade approach that causes it to drop associated keys is still faster than asking you to do 10mil deletes.
If you are going to overload it, it will depend a lot on your machine. But it is always interesting to create indexes for the keys of all associated tables. Further reading: link
