Database overloads while deleting lines with relationships?

Question

Good afternoon to all!

I have a question, and I’d like to get some opinions.

I’m building a system in php + Larable.

I have relationships between tables, for ex: Users->Roles.

They are supposed to exist 10,000 users registered with the Role == Admin. If I exclude the Role Admin, the system would theoretically have to exclude 10,000 table rows role_users for the Role Admin will cease to exist.

That would generate 10,000 darlings? It would overload the comic?

Hugs

Answer 1

In any relational system, if you have a relation indicating that the parent element deletion removes the child elements in cascade, the deletion of a single parent element and all your children will occur in a single query.

Whether or not this will overload the database depends on the cascade removal flow and the size of the mass of data to erase. Your user records may have other relationships with your own children, which can also be cascaded away, for example. But in general, removing is fast. Especially if the relationship between the elements has indexing in the column of the "child" side of the relationship.

The whole weight is in the database processing. For the application, a single query on a single connection is executed.

Answer 2

It will generate yes querys to delete and will generate extra work to lift all the places that should be deleted together. But this on delete approach that makes it drop the associated keys is still faster than if you ask to do 10mil Deletes.

If you’re going to overload it, it’s not going to say, it’s going to depend a lot on your machine. But it is always interesting to create Index keys for all associated tables. Further reading: http://www.codesynthesis.com/~Boris/blog/2012/04/12/Explicit-sql-delete-vs-on-delete-Cascade/