Jump to content
#StayAtHome ×
shades

Delete query takes too long and also does not delete all records

Recommended Posts

Hi Guys,

 

I have a form where the user can select rows and delete them. But my problem is when I select around 4800 rows to delete it takes too long and also only 2500 rows get deleted. So, could someone help me with the below code so it does not take more time and also deletes all the rows?

 

Note:

1. I use InnoDB

2. I use ajax with async set to false

<?php
    include("dbconnect.php");    
    ini_set ( 'max_execution_time', 20000); 

    if(isset($_POST['id'])){
		foreach ($_POST['id'] as $ids) {
			$stmt = $dbconnect->prepare("DELETE FROM datatable WHERE id=:id");
		    $stmt -> bindValue(':id', $ids);
		    $stmt -> execute(); 
	    }
    $data =1;
    echo $data;
   }
?>

I see in the above code my delete query is executed again and again through the loop and I would like to know if there is a way to execute only one statement and get all the ids inside the statement.

 

Thank you.

Share this post


Link to post
Share on other sites

Where are the 4800 ids coming from? I can't imagine someone entering them into a form.

Share this post


Link to post
Share on other sites

It is a table which displays those rows and user has checkboxes against each row and also a select all option so if they select all rows are selected, so in my example, i had 4800 rows

Share this post


Link to post
Share on other sites

OK, so those checkboxes have ids as their values.

 

So asking my original question, where do those ids come from?

 

EDIT:

 

As giving us information is painful for you I'll give you the solution for you to work on yourself -

 

Use a single multitable delete query which joins the source table of the ids with the table you are deleting from.

Edited by Barand

Share this post


Link to post
Share on other sites

I got the data table with primary key as a running number and when I display data to the user I pick these ids as well. So when the user checks the boxes the id's get picked and all id's are stored in the id array. So this delete query is used to delete the rows with matching id's. 

 

Sorry, my English ain't good so I understood your first question in a different way.

Share this post


Link to post
Share on other sites

Alternativly, use a single delete query using IN() function

 

EG

 

DELETE FROM datatable WHERE id IN (1,2,3,4,5,6, ... , 4800)

Share this post


Link to post
Share on other sites

Alternativly, use a single delete query using IN() function

 

EG

 

DELETE FROM datatable WHERE id IN (1,2,3,4,5,6, ... , 4800)

 

Yea got it working partially now, the delete happens within few milliseconds, but I am not understanding why it deletes only 2500 rows. Is there any setting which I need to change? I did try executing the query with all 4800 ids directly using MySQL workbench and all rows were deleted. So, I feel there is something from the client side which is limiting this.

Share this post


Link to post
Share on other sites

Try deleting chunks of 1000 at a time instead of 4800 all i a single query.

 

NOTE: If 4800 is the total of all records in the table then instead of deleting all single records in a table it is far quicker to truncate the table.

Share this post


Link to post
Share on other sites

Ok i found out the issue when i pass the array using ajax it shows 4800, when i do a count on the received array it only shows 2500. So, I finally found the culprit it was in php.ini file.

 

; How many GET/POST/COOKIE input variables may be accepted
max_input_vars = 2500
 
damn.

Share this post


Link to post
Share on other sites

Rather than selecting/deleting by individual ids, are there no other criteria than could be used by the user, such as all records older than X days/months/years or any other attribute hey may have in common?

Share this post


Link to post
Share on other sites

Rather than selecting/deleting by individual ids, are there no other criteria than could be used by the user, such as all records older than X days/months/years or any other attribute hey may have in common?

 

No. And yes even changing it in php file did not do the trick i actually had to json stringify while passing through AJAX and then had to decode it on my php file.

Share this post


Link to post
Share on other sites

FYI: If you are going to use the IN() condition you will need to either programatically build the prepared query or add logic to ensure 100% that no malicious data is included. A quick and dirty solution would be as follows

 

//Convert all values to integers (intval) and excludes those that return false
$deleteIds = array_filter('intval', $_POST['id']);
 
//Create query
$query = "DELETE FROM datatable WHERE id IN (" . implode(",", $deleteIds) . ")";
$dbconnect->query($query);
  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.