[PHP] Too many DELETE statements
I have 160,000+ members in my site. 40,000 of them have not logged in since 2003 and they should be deleted. A member is not deleted from only one table. Based on user id, he should be deleted from 5 tables and also his photo, if any, should be unlink(ed). I tried to do that 10 by 10 using: $query = delete from table1 where userid = . $ID; $result = mysql_query($query, $link); $query = delete from table2 where userid = . $ID; $result = mysql_query($query, $link); ... But even with only 10 members, the page takes 30-60 seconds to come back to me. What is the best way to accomplish this? And it is possibe to delete 1000 by 1000 or 100 by 100? Thanks. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
news.php.net wrote: A member is not deleted from only one table. Based on user id, he should be deleted from 5 tables and also his photo, if any, should be unlink(ed). $query = delete from table1 where userid = . $ID; $result = mysql_query($query, $link); $query = delete from table2 where userid = . $ID; $result = mysql_query($query, $link); ... But even with only 10 members, the page takes 30-60 seconds to come back to me. What is the best way to accomplish this? And it is possibe to delete 1000 by 1000 or 100 by 100? This could be fixed by changing your database schema. You should have your main table, lets call it members, where userid is the primary key. All your other tables that use userid should reference members.userid as a foreign key with ON DELETE CASCADE set. This will make sure that any time a userid is deleted from members, the delete will cascade to all other tables that contain userid. See your databases documentation CREATE TABLE and ALTER TABLE syntax. -- David Dickson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
news.php.net wrote:
I have 160,000+ members in my site. 40,000 of them have not logged in
since
2003 and they should be deleted. A member is not deleted from only one
table. Based on user id, he should be deleted from 5 tables and also his
photo, if any, should be unlink(ed).
I tried to do that 10 by 10 using:
Ah. Don't do that.
Do this:
$query = delete from table1 where userid in (select userid from
login_table where last_login = '2003-12-31');
ANY time you are using PHP to loop through record after record after
record in the database, and then you are sending a new query for every
record you find, you DOING IT WRONG. :-)
SQL is *VERY* good at describing exactly which records should have
something done to them, and doing it, or finding them, or whatever.
PHP is not so fast at that.
Oh. If your version of MySQL doesn't do sub-selects, you'll want to do:
$query = select userid from login_table where last_login = '2003-12-31';
$goners = mysql_query($query, $link) or trigger_error(@mysql_error($link)
. $query, E_USER_ERROR);
$ids = array();
while (list($userid) = @mysql_fetch_row($goners)){
$ids[] = $userid;
}
$ids_sql = implode(, , $ids);
$query = delete from table1 where userid in ($ids_sql);
mysql_query($query, $link) or trigger_error(@mysql_error($link) .
$query, E_USER_ERROR);
You can repeat that for each table.
If it turns out that having 40K IDs in the array/string is too much, just
add a LIMIT clause to the first query:
$query = select userid from login_table where last_login = '2003-12-31'
limit 100;
You'll have to reload the page 40 times. Or, better yet, once you're
comfy with the page working for 100 peeps, just wrap a for($i = 0; $i
40; $i++) around the whole script.
Needless to say, if you *DO* use the sub-select, you'll have to delete the
records from the table that keeps track of last_login *LAST* :-)
You may also want to archive the 40,000 users somewhere, just in case...
Or even put them into a user_dormant table or something, so you can pull
them back from the grave quickly if they want to re-activate their
account.
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
David Dickson wrote: news.php.net wrote: A member is not deleted from only one table. Based on user id, he should be deleted from 5 tables and also his photo, if any, should be unlink(ed). $query = delete from table1 where userid = . $ID; $result = mysql_query($query, $link); $query = delete from table2 where userid = . $ID; $result = mysql_query($query, $link); ... But even with only 10 members, the page takes 30-60 seconds to come back to me. What is the best way to accomplish this? And it is possibe to delete 1000 by 1000 or 100 by 100? First, you're sending a total of: 10 users X 5 tables == 50 queries to the database. That's a bit much, really. Plus you are unlink-ing 10 files. That's probably the real problem. You'd have to write some timing code to be sure, though, as a slow database server and a very fast hard drive could be involved. Here are some things you could do to speed it up, assuming you don't want the ON DELETE CASCADE option, or if your database doesn't provide that option. 1. Send only one query for each table: You should be able to collect all the $ID values in one list like this: $ids_sql = 2, 4, 5, 42, 17, 68, 1, 9, 10; $query = select from table1 where userid in ($ids_sql); Of course, you'll need to write that to handle your incoming FORM data rather than hard-coding the IDs. The other thing is unlink-ing the image. That is probably the bigger time-sink than just a few (dozen) queries. One way to beat this is to *NOT* unlink the file in your script. And the ON DELETE CASCADE won't fix this at all. Instead, write a cron job to walk through the images and throw away anything not being used. This will be slower and less efficient than doing it in the script, but it can be a background process, not making the user wait for what is essentially a house-cleaning project. Actually, you could have a table of deleted_users and *INSERT* any ID after you delete it from the other 5 tables. Then your cron job would just delete the images corresponding to the users in that deleted_users table, and, of course, delete their ID from that table. The point here is to separate out work that *MUST* be done immediately for the user to have a good experience, and the house-cleaning chores you need to do that won't affect the user experience at all. An unused image file cluttering up the hard drive for a few hours won't (in almost all cases) have any real effect on the user. Unlink the file later in a cron job, and get the user's perceived time for the delete down to a nice experience. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
Thanks alot. You really gave me some good ideas.
Lordo
Richard Lynch [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
news.php.net wrote:
I have 160,000+ members in my site. 40,000 of them have not logged in
since
2003 and they should be deleted. A member is not deleted from only one
table. Based on user id, he should be deleted from 5 tables and also his
photo, if any, should be unlink(ed).
I tried to do that 10 by 10 using:
Ah. Don't do that.
Do this:
$query = delete from table1 where userid in (select userid from
login_table where last_login = '2003-12-31');
ANY time you are using PHP to loop through record after record after
record in the database, and then you are sending a new query for every
record you find, you DOING IT WRONG. :-)
SQL is *VERY* good at describing exactly which records should have
something done to them, and doing it, or finding them, or whatever.
PHP is not so fast at that.
Oh. If your version of MySQL doesn't do sub-selects, you'll want to
do:
$query = select userid from login_table where last_login =
'2003-12-31';
$goners = mysql_query($query, $link) or trigger_error(@mysql_error($link)
. $query, E_USER_ERROR);
$ids = array();
while (list($userid) = @mysql_fetch_row($goners)){
$ids[] = $userid;
}
$ids_sql = implode(, , $ids);
$query = delete from table1 where userid in ($ids_sql);
mysql_query($query, $link) or trigger_error(@mysql_error($link) .
$query, E_USER_ERROR);
You can repeat that for each table.
If it turns out that having 40K IDs in the array/string is too much, just
add a LIMIT clause to the first query:
$query = select userid from login_table where last_login = '2003-12-31'
limit 100;
You'll have to reload the page 40 times. Or, better yet, once you're
comfy with the page working for 100 peeps, just wrap a for($i = 0; $i
40; $i++) around the whole script.
Needless to say, if you *DO* use the sub-select, you'll have to delete the
records from the table that keeps track of last_login *LAST* :-)
You may also want to archive the 40,000 users somewhere, just in case...
Or even put them into a user_dormant table or something, so you can pull
them back from the grave quickly if they want to re-activate their
account.
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
Richard Lynch wrote: Plus you are unlink-ing 10 files. That's probably the real problem. You'd have to write some timing code to be sure, though, as a slow database server and a very fast hard drive could be involved. Here are some things you could do to speed it up, assuming you don't want the ON DELETE CASCADE option, or if your database doesn't provide that option. You should do this (even if you don't want to) because it is good design. If when a member is deleted you want all associated tables to also delete this member information then you should set up your foreign keys properly. 1. Send only one query for each table: You should be able to collect all the $ID values in one list like this: $ids_sql = 2, 4, 5, 42, 17, 68, 1, 9, 10; $query = select from table1 where userid in ($ids_sql); Of course, you'll need to write that to handle your incoming FORM data rather than hard-coding the IDs. The other thing is unlink-ing the image. That is probably the bigger time-sink than just a few (dozen) queries. One way to beat this is to *NOT* unlink the file in your script. And the ON DELETE CASCADE won't fix this at all. Instead, write a cron job to walk through the images and throw away anything not being used. This will be slower and less efficient than doing it in the script, but it can be a background process, not making the user wait for what is essentially a house-cleaning project. Something else you could do is to build one big rm statement and run it in the background. This would save you building a cron job which sometimes isn't possible depending on the hosting arrangement. ?php $Remove = rm $ImageLocation1; rm $ImagLocation2; rm $ImageLocation3; exec(nohop $Remove /dev/null 21 ); ? Where the $ImageLocationx is the full path to the image you want to delete. The at the end of the exec tells the command to run in the background, which means your script won't wait for the command to finish before continuing. The output redirection ( /dev/null 21) is also necessary to allow the script to continue. See the PHP documentation on exec for more details. -- David Dickson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
Thanks. I will check the foreign key with cascading issue. But I have a question: Will it have any bad effects on behavior? I have tables with 160,000, 400,000, etc. records. Lordo David Dickson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] news.php.net wrote: A member is not deleted from only one table. Based on user id, he should be deleted from 5 tables and also his photo, if any, should be unlink(ed). $query = delete from table1 where userid = . $ID; $result = mysql_query($query, $link); $query = delete from table2 where userid = . $ID; $result = mysql_query($query, $link); ... But even with only 10 members, the page takes 30-60 seconds to come back to me. What is the best way to accomplish this? And it is possibe to delete 1000 by 1000 or 100 by 100? This could be fixed by changing your database schema. You should have your main table, lets call it members, where userid is the primary key. All your other tables that use userid should reference members.userid as a foreign key with ON DELETE CASCADE set. This will make sure that any time a userid is deleted from members, the delete will cascade to all other tables that contain userid. See your databases documentation CREATE TABLE and ALTER TABLE syntax. -- David Dickson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
Thanks guys. I delete 500 by 500 now and it takes like 20 seconds only. I am using the manual select where in method. It is great. Now for the files, OK I will use a cron. But can I change the way I get the file names? I mean instead of deleting the photo that is related to a deleted member, can I delete photos that were last accessed a year ago? Lordo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
David Dickson wrote: Richard Lynch wrote: Plus you are unlink-ing 10 files. That's probably the real problem. You'd have to write some timing code to be sure, though, as a slow database server and a very fast hard drive could be involved. Here are some things you could do to speed it up, assuming you don't want the ON DELETE CASCADE option, or if your database doesn't provide that option. You should do this (even if you don't want to) because it is good design. If when a member is deleted you want all associated tables to also delete this member information then you should set up your foreign keys properly. But you may not want this to *ALWAYS* happen in your business logic. I don't know enough about the application he's writing to say for sure either way. You'd think that you wouldn't want related records hanging around when deleting a user, but sometimes one does. Just depends on the application. Something else you could do is to build one big rm statement and run it in the background. This would save you building a cron job which sometimes isn't possible depending on the hosting arrangement. ?php $Remove = rm $ImageLocation1; rm $ImagLocation2; rm $ImageLocation3; exec(nohop $Remove /dev/null 21 ); nohop? Maybe you mean nohup?... Or maybe I need to go read man nohop... :-) I've found inconsistent results with trying to fork in the shell from PHP -- Again this may go back to earlier versions, but I don't know that this will work for sure in all versions. Where the $ImageLocationx is the full path to the image you want to delete. The at the end of the exec tells the command to run in the background, which means your script won't wait for the command to finish before continuing. The output redirection ( /dev/null 21) is also necessary to allow the script to continue. See the PHP documentation on exec for more details. The downside is that it makes this difficult to debug. Probably better to re-direct the errors *somewhere* so you have a chance at debugging things when (not if, when) they break. Though it probably takes care of whatever was messing me up back in the day when I was trying to get exec() to fork. I must say, though, I've found over time that setting up a cron job to take care of this kind of stuff usually works better for me and the user experience than exec/fork. Even with the to fork, exec is not all that fast, I don't think. A quick insert to a small table so that a cron job can unlink (and delete from the table) later works better/faster for me. YMMV. Just my opinions, of course. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Too many DELETE statements
Lordo wrote: Thanks guys. I delete 500 by 500 now and it takes like 20 seconds only. I am using the manual select where in method. It is great. Now for the files, OK I will use a cron. But can I change the way I get the file names? I mean instead of deleting the photo that is related to a deleted member, can I delete photos that were last accessed a year ago? Sure. Every Linux file has a file times recorded when it was last accessed, and last had *anything* including permissions etc modified, and when it was originally created. But this might be real dangerous. Somebody might have a photo they want to keep that you are about to nuke... Also be sure to read the caveat in the docs about system where fileatime is NOT getting changed for performance reasons. http://php.net/fileatime You'd probably be better off to nuke the photos of the deleted users, and then see where you really stand with out-dated/unused photos. Might be a lot less than you think. Another option is to write a shell script (or PHP) to find really HUGE photos that goofballs have uploaded, or users who have *wy* too many photos, and harass them individually. It's usually the case that one or two users are clogging up 90% of your resources when you start digging into this stuff. :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
