Jump to content

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


shades

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.

Link to comment
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.

Link to comment
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.

Link to comment
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.

Link to comment
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.
Link to comment
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.

Link to comment
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);
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.