Jump to content


Photo

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

delete mysql php

  • Please log in to reply
11 replies to this topic

#1 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 05:19 PM

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.



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,164 posts

Posted 29 November 2017 - 05:42 PM

Where are the 4800 ids coming from? I can't imagine someone entering them into a form.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 05:46 PM

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



#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,164 posts

Posted 29 November 2017 - 05:56 PM

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, 29 November 2017 - 06:01 PM.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 06:13 PM

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.



#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,164 posts

Posted 29 November 2017 - 06:33 PM

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

EG

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 06:54 PM

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.



#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,164 posts

Posted 29 November 2017 - 07:12 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 07:48 PM

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.


#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,164 posts

Posted 29 November 2017 - 08:27 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 shades

shades
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 29 November 2017 - 08:32 PM

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.



#12 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,936 posts
  • LocationCanada

Posted 29 November 2017 - 09:31 PM

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

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users