How to delete thousands of rows with PHP and MySQL in background?

Question

I have a website which people post images to, people can like the post for every like there is a row in a table, if someone deletes a post I would like to delete every row for that specific post. The number can be very large like 15,000 or more, the time waited can be long for the user that deleted the post.

To delete the post there is a form I would like to delete the 15,000 rows in another script in the background. How can I achieve that?

Disclimax

If you have foreign key constraints, then you can DELETE CASCADE: but regardless of that, make sure that you have decent indexing and that mass deletion shouldn't be a major problem – Alwitt

how about mysql create event functionality. Set a flag. the event picks it up (you choose the interval). Built-in cron functionality. Something like this that I wrote up. In your event your delete statement(s) would occur with or without cascading depending on your setup – Cordell

I'd just flag the post as deleted and would set a cron job for cleaning all the likes related to all deleted posts – Pallet

Show us your database structure and any code yo already have – Behring

@MarkBaker I've actually found cascading is incredibly slow. Better to explicitly delete rows ahead of the cascade. Might be due to bad config. – Freezing

Agree with Berriel that handling the deletes asynchronously is the way to go, but a cron job would not be my first choice. A more important question is whether there is a net benefit from deleting the data. – Columella

Answer 1

Lots of options. This is more of an architectural/engineering decision than anything else. I'll throw out an idea.

Start with soft deletes. Instead of actually deleting the post (and its relationships), set a status to deleted. Have a CRON job setup during an off time to run a query to get posts with a status of deleted and remove everything for good at that time.

Just noticed @Berril suggested the same in his comments.

On a side note, would it really take that long to delete 15,000 rows?

DELETE FROM `table` WHERE post_id = x

Seems like that would execute quickly, but without seeing the data structure, hard to say for sure.

Marcel

+1 for recommending a soft delete. to add to that, the OP could have an archive table that you could migrate the "deleted" post to. that way you can still have accurate reporting for internal purposes. Deleting in general isn't a great idea, but there are some cases where it is. – Reneta

The problem with any approach with a simple DELETE statement is that it doesn't take into consideration all the indexes and the query cache that have to be rebuilt. – Academic

Answer 2

Soft delete + cronjob recommend:

I don't recommend a fully deletion of the records except you have to as of data privacy e.g.

However, as been mentioned already add to each table a column like deleted and when a record has to be deleted you set the value in this column to true.

After this you can develop a cronjob which will be triggered. E.g. on a daily base during midnight.

A cronjob usually will be executed on a shell and has not the short time out as a browser has.

In your case: don't delete all data when image has been removed:

When you are dealing with just a few records it is legit to delete all records fully when a user wants to delete them.

However, in your case there are probably thousands of records which has to be deleted. So, when you try to delete all of them when a user deletes a certain image it surely takes time and probably end in a time out. Doing it via ajax is not good as users don't want to wait until all ajax-requests has been finished.

Molliemollify

Answer 3

Use Ajax. try following script to request another page to delete rows in background.

<script>
        var con=new XMLHttpRequest();
        con.onreadystatechange=function()
        {
            if(con.readyState==4 && con.status==200)
                div.innerHTML+=con.responseText+"</br>";
        }
        con.open("POST","db.php",true);
        con.send();
</script>

and write the following code in db.php. I have assumed that db.php is in same directory where your current html file is:

$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


$run = mysqli_query($conn,"[Your Query here]");
print "Your Post Deleted Successfully!";

Monochrome

The page calling ajax will navigate away, closing the ajax request. The request will probably finish on the server, but the response wouldn't be received by the page. Not a bad idea though. Lazy cron can work like this. – Freezing

Answer 4

If you run PHP in fastcgi mode (usually nginx), you can use fastcgi_finish_request() to finish the user's request, while still doing things in the background, like slowly deleting many records that don't require user confirmation.

• fastcgi & nginx
• fastcgi & apache

Freezing