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?

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.

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.

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!";

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