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

[shades]

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.

[Barand]

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

[shades]

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

[Barand]

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 November 29, 2017 by Barand

[shades]

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.

[Barand]

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

 

EG

 

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

[shades]

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.

[Barand]

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.

[shades]

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.

[Barand]

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?

[shades]

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.

[Psycho]

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);