3 ms·

thats why I never have a DELETE in any query, only UPDATE and a state field (ie. deleted) performance advantage here as well since indexes aren't rebuilt and n

by bl4k 16y ago

thats why I never have a DELETE in any query, only UPDATE and a state field (ie. deleted) performance advantage here as well since indexes aren't rebuilt and no table lock

joshu 16y ago
Indexes cab certainly update. Pretty sure innodb does not table lock for delete either. Also from a privacy perspective you can't keep people's data around forever.
- portman 16y ago
  Maybe this is overly pedantic, but you can do whatever you want with people's data, so long as you inform them of your policies and they agree to them.
  - heyitsnick 16y ago
    I doubt that's true, at least in the UK. Terms and conditions cannot trump your personal rights. We have these terms laid out in the Data Protection Act and I doubt you can sign this away be agreeing to a website policy that contradicts them.
    - albertzeyer 16y ago
      Same Thing in Germany. The Law always has priority over whatever you write.
    - portman 16y ago
      I just read the 1998 Data Protection Act, and it is very similar to dozens of laws that govern data privacy in the US. There are 8 specific directives in the law. #1-6 are about consent, #7 is about security, and #8 is about correcting inaccuracies. So again, if a website owner clearly informs the end-user about their policies, and the end-user agrees then, the website owner is in compliance with law. The specific example that motivated my point -- "you can't keep people's data around forever" -- is simply not true in the US or in the UK (if the '98 DPA is the only applicable law; there may be others I'm not aware of).
- bl4k 16y ago
  even with innodb you will still find yourself running optimize. checkout what wordpress does (from wp-content/plugins/akismet/akismet.php): if ( (mt_rand(1, 10) == 3) ) { // WP 2.0: run this one time in ten akismet_delete_old(); } and then in that function, after the DELETE, is this: $wpdb->query("OPTIMIZE TABLE $wpdb->comments"); I am sure there are plenty of people out there having fun trying to work out why there tables suddenly lock and they see an optimize process running randomly. I am also sure it runs fine in their unit tests when they have 1 post and 2 comments. all this because they DELETE :) They have all comments, those that have been approved, those that are in moderation, and all spam, in the same table - so if they don't delete the table would become unmanagable, so it is the design at fault and the wrong solution. If you search source at github or somewhere similar, you will find projects with OPTIMIZE everywhere - solving a real problem entirely the wrong way I got used to it because the advantages just far outweigh the disadvantages. Records don't disappear for malicious reasons or because of mistakes - you can purge records marked delete every 30 days with a background process, if you like - but I no longer, ever, type that keyword into an app.

by bl4k 16y ago

thats why I never have a DELETE in any query, only UPDATE and a state field (ie. deleted) performance advantage here as well since indexes aren't rebuilt and no table lock

joshu 16y ago

Indexes cab certainly update. Pretty sure innodb does not table lock for delete either. Also from a privacy perspective you can't keep people's data around forever.

portman 16y ago
Maybe this is overly pedantic, but you can do whatever you want with people's data, so long as you inform them of your policies and they agree to them.
- heyitsnick 16y ago
  I doubt that's true, at least in the UK. Terms and conditions cannot trump your personal rights. We have these terms laid out in the Data Protection Act and I doubt you can sign this away be agreeing to a website policy that contradicts them.
  - albertzeyer 16y ago
    Same Thing in Germany. The Law always has priority over whatever you write.
  - portman 16y ago
    I just read the 1998 Data Protection Act, and it is very similar to dozens of laws that govern data privacy in the US. There are 8 specific directives in the law. #1-6 are about consent, #7 is about security, and #8 is about correcting inaccuracies. So again, if a website owner clearly informs the end-user about their policies, and the end-user agrees then, the website owner is in compliance with law. The specific example that motivated my point -- "you can't keep people's data around forever" -- is simply not true in the US or in the UK (if the '98 DPA is the only applicable law; there may be others I'm not aware of).
bl4k 16y ago
even with innodb you will still find yourself running optimize. checkout what wordpress does (from wp-content/plugins/akismet/akismet.php): if ( (mt_rand(1, 10) == 3) ) { // WP 2.0: run this one time in ten akismet_delete_old(); } and then in that function, after the DELETE, is this: $wpdb->query("OPTIMIZE TABLE $wpdb->comments"); I am sure there are plenty of people out there having fun trying to work out why there tables suddenly lock and they see an optimize process running randomly. I am also sure it runs fine in their unit tests when they have 1 post and 2 comments. all this because they DELETE :) They have all comments, those that have been approved, those that are in moderation, and all spam, in the same table - so if they don't delete the table would become unmanagable, so it is the design at fault and the wrong solution. If you search source at github or somewhere similar, you will find projects with OPTIMIZE everywhere - solving a real problem entirely the wrong way I got used to it because the advantages just far outweigh the disadvantages. Records don't disappear for malicious reasons or because of mistakes - you can purge records marked delete every 30 days with a background process, if you like - but I no longer, ever, type that keyword into an app.