0

I want to create something like Snapchat that will automatically delete row after 24 hours in php/mysql. I have read about cron jobs,but it slows down apps,so i was wondering if there is other way to do it?

Murad
  • 167
  • 1
  • 2
  • 13
  • `but it can't run on big apps` Really, and why? – u_mulder Nov 19 '16 at 21:05
  • @u_mulder excuse my vagueness.I edited it:) – Murad Nov 19 '16 at 21:06
  • 1
    Now - `it slows down apps`. How? Who told you that? – u_mulder Nov 19 '16 at 21:08
  • http://wordpress.stackexchange.com/questions/125995/do-wordpress-cron-jobs-slow-down-page-loading,@u_mulder – Murad Nov 19 '16 at 21:10
  • http://stackoverflow.com/questions/22537426/cron-jobs-over-100-per-minute-too-many @u_mulder – Murad Nov 19 '16 at 21:10
  • 1
    You don't even have a project and you consider it so `big` that cron will slow it. Funny. – u_mulder Nov 19 '16 at 21:11
  • You could use MySQL `EVENTS` https://dev.mysql.com/doc/refman/5.7/en/create-event.html – Qirel Nov 19 '16 at 21:12
  • @u_mulder if i have and somehow it becomes big?Sometimes you just have to accept loosing. – Murad Nov 19 '16 at 21:12
  • Then where's your questions about load balancing, horizontal/vertical sharding, cdn providers, etc))) – u_mulder Nov 19 '16 at 21:14
  • @Qirel Thanks!I never knew about `events`. – Murad Nov 19 '16 at 21:15
  • You use the word cron followed by something that illustrates you don't know what it means. If your database is sanely designed it's trivial to delete records via cron triggered scripts.. If your database design and/or your maintenance script is dumb then yes, it will be bad but cron has nothing to do with that. – Duane Lortie Nov 19 '16 at 21:16
  • @DuaneLortie i suppose it will be better to us `Events`,wouldn't you say so? – Murad Nov 19 '16 at 21:17
  • Creating and maintaining MySQL events can be a bit of a dark art. Also there's nothing "slow" about `cron` jobs. If you're doing a huge `DELETE` operation it will take time, even if you trigger it with an event. You have zero rows in your database today. Don't worry about performance until you have a measurable problem. – tadman Nov 19 '16 at 21:29
  • At approx how many rows does cron job become problem then? @tadman – Murad Nov 19 '16 at 21:32
  • It's not the method of executing the command that's the problem, `cron` or otherwise. It's how complex your table is and how expensive the `DELETE` is. The only way to know that is to test. – tadman Nov 20 '16 at 01:54
  • Old but I just want to mention that the user linked to "wordpress crons" which are software level cronjobs that execute when the next page load happens and does some work first before rendering the page. That does slow down page load if they're doing too much. Real cronjobs are system level shell scripts that get executed in the background of the server completely separate from your application and will not impact performance of your app. – RedactedProfile Jan 23 '20 at 18:44

3 Answers3

6

What you're looking at is something effectively like this:

DELETE FROM snaps WHERE created_at <= DATE_SUB(NOW(), INTERVAL 1 DAY)

So long as you populate created_at and index it, this should run fairly quickly. If you have lots of records, and by that I mean over 100 million, you'll need to space that out:

DELETE FROM snaps ... LIMIT 10000

Where you can do it in chunks of 10K or whatever works best.

Postgres uses a slightly different notation:

DELETE FROM table WHERE created_at <= NOW() - INTERVAL '1' DAY;
tadman
  • 208,517
  • 23
  • 234
  • 262
2

To your question, there are many ways to do it:

  1. Cron job (external to your app)
  2. In-app (server side) job (new EvTimer() in php, setInterval() in node/js, javax.swing.Timer in Java, ETC.)
  3. Thread + sleep (please don't do that)
  4. Alarm signal (you should probably not)

But instead of deleting on an interval and relying on it, I suggest the below, it will also give you correctness of exact 24h (you might not require it, but it is good anyway):

  • your records need to be timestamped (almost every solution will require this)
  • when you read the records, filter out records that the timestamp is less than now minus 24 hours
  • delete the old records (cleanup) on an interval, say once a day, using cron job oe in-app job.
niry
  • 3,238
  • 22
  • 34
1

While it would certainly be a bad idea to add a cron job for every row of your database, I don't see a problem of running a cron job running (e.g.) every hour (or every 30mins, or once a day), which deletes every entry older than 24 hours.

You can furthermore make sure that no entries older than 24 hours are ever returned when querying them.

chtz
  • 17,329
  • 4
  • 26
  • 56