----- Original Message -----
From: "Basil Hussain" <[EMAIL PROTECTED]>
To: "Mark" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, October 22, 2002 6:32 PM
Subject: RE: Table gets wiped out!
> Hi,
>
> > $sth = $dbh -> prepare ("LOCK TABLES $table WRITE");
> > die $dbh->errstr if (not defined ($sth -> execute));
> >
> > $sth = $dbh -> prepare ("DELETE FROM $table");
> > die $dbh->errstr if (not defined ($sth -> execute));
> >
> > This truncated the table. Then I filled the table anew, and, at the
> > end (before the UNLOCK), issued:
> >
> > $sth = $dbh -> prepare ("OPTIMIZE TABLE $table");
> > die $dbh->errstr if (not defined ($sth -> execute));
>
> One question... why are you bothering to optimize the table after
> inserting new rows? In this case, there is no need!
>
> Why? Well, when you issue the DELETE statement with no WHERE clause,
> MySQL simply drops and re-creates your table, rather than deleting every
> row individually.
It does? That is not what I want. If I wanted to drop the table, I would
have done a DROP TABLE. The reason I delete all entries with an EMPTY WHERE
clause, is so I can keep a LOCK on the table during the entire update
process. And I would lose the LOCK if I were to drop it in-between.
Does it also drop the indexes for it? That would be real bad. I just want
the table emptied and refilled; I do not want the indexes dropped as well!
> Therefore, when you are inserting new rows, it is effectively
> a fresh table, with no wasted space from previously deleted rows.
> So, you are not re-using any deleted rows at all.
Kewl to know.
The issue still remains, of course, that after the OPTIMIZE (whether
sensible or no), the entire table appears to be wiped out! And that because
of the earlier LOCK. So, if I LOCK the table, then effectively drop the
table (with "DELETE FROM $table"), refill the new table, and afterwards
OPTIMIZE and UNLOCK the table, then the entire table has disappeared!
I still cannot explain that; and I am still worried by it.
Kind regards,
- Mark
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
