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
