Re: 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" <davidd.lists@magma.ca> wrote in message news:41AF34A1.1090309@magma.ca... > 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