-1

I have had a question for some time regarding MySQL queries. I have designed a system that requires that when the user deletes a post there should also be a deletion of information from other tables. The question comes down to this: Am I better off using a method that deletes all of the rows from multiple tables in ONE query or is it more resource intensive to perform multiple queries to perform the same action? One of the reasons for my question is this post: Mysql - delete from multiple tables with one query. It seems from the responses that experienced coders feel it is unnecessary to perform the action in one query. In fact, from this post it appears that the approach of deleting multiple rows from several tables in one query was MORE resource intensive: How to delete from multiple tables in MySQL?. My thoughts have always been that if I can do all of it in one query it would be less resource intensive. This has been my general approach to CRUD - though I now have doubts as to how relevant that approach is. Thank you in advance for your help!

Community
  • 1
  • 1
user791187
  • 657
  • 3
  • 11
  • 23
  • The only answer I can really think of is "it depends". – Jonnix May 24 '16 at 13:29
  • Which resources? Purely those on the database, or on the calling server. The resources to send a request to the server can be significant. – Kickstart May 24 '16 at 13:54
  • @Kickstart - thank you. Is that to say local processing of the request that is intensive? So would you recommend only making one query? – user791187 May 25 '16 at 21:57
  • @JonStirling would you please elaborate? – user791187 May 25 '16 at 21:57
  • 1
    Local processing is not (generally) intensive. But the connection, passing of the request to the database server and parsing of the query can be. You can use prepared statements which should minimise the parsing overheads (ie, should only be required for the first delete subsequent ones should be recognised as the same). The only real extra overhead of a single query is a marginally more complicated query, and the possibility of a large number of generated temp rows if you are deleting from multiple tables which have 1 to many relationships. – Kickstart May 26 '16 at 08:45
  • 1
    For an example of 1 to many causing problems, say you had a users table with a logins table (listing say 10000 logins for that user) and a posts table (listing say 10000 posts for that user). That generates 100,000,000 intermediate rows. – Kickstart May 26 '16 at 08:47

1 Answers1

1

Why not use foreign keys with ON DELETE CASCADE? Then MySQL itself will remove all related rows automatically.

If you need to delete it manually, I'd go with multiple DELETE statements in a transaction.

Shira
  • 6,392
  • 2
  • 25
  • 27
  • This is really great information - and something I will certainly look more into. It will likely solve many of the problems I am now looking at. My question is more towards why or why not to make multiple queries vs just one. – user791187 May 25 '16 at 21:59
  • @user791187 1) Multiple DELETE queries are easier to read and maintain. 2) The syntax to remove from multiple tables at once is not standard SQL and differs between RDBMS's. 3) Removing rows usually isn't the bottleneck of an application. – Shira May 25 '16 at 22:42