SitePoint Sponsor |
|
User Tag List
Results 1 to 2 of 2
-
May 19, 2003, 07:08 #1
- Join Date
- May 2001
- Location
- Sydney
- Posts
- 170
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
truncate and optimize via php no go bro
Hi all,
I am doing a loop out of a temp table, moving some of that data to another table, then deleting the row from the temp table - works a treat. When the temp table is empty, I want to clean it up.
After the temp table is empty and has no rows, I would like to 'truncate' it to make sure cardinality or next autoindex returns to 1. Here lays the weird-dom...
I can truncate this table and it works.
THENI try to do the same to another table and nothing happens. My applicable code...
PHP Code:// ----------- CLEAN-UP THE TABLES IF EMPTY
$cleanup = ("SELECT batchID from queue");
$result3 = mysql_query($cleanup);
if(mysql_num_rows($result3) == 0) {
mysql_query("TRUNCATE queue");
} // <-- This WORKS GOOD!!!!
$cleanup2 = ("SELECT batchID from batch");
$clnp = mysql_query($cleanup2);
if(mysql_num_rows($clnp) == 0) {
mysql_query("TRUNCATE batch");
} <-- THIS BAD
Cheers,
Mick"You know what you know - but that's all you know!"
-
May 19, 2003, 07:15 #2
- Join Date
- Jul 2002
- Location
- Along the Wasatch Fault line.
- Posts
- 1,771
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Try "TRUNCATE TABLE batch" and try changing the first one to the same syntax, "TRUNCATE TABLE queue".
TRUNCATE Syntax
TRUNCATE TABLE table_name
In 3.23 TRUNCATE TABLE is mapped to COMMIT ; DELETE FROM table_name. See DELETE.
TRUNCATE TABLE differs from DELETE FROM ... in the following ways:
· Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.
· Not transaction-safe; you will get an error if you have an active transaction or an active table lock.
· Doesn't return the number of deleted rows.
· As long as the table definition file table_name.frm is valid, the table can be re-created this way, even if the data or index files have become corrupted.
TRUNCATE is an Oracle SQL extension.
MySQL Reference Manual (C) 2002 MySQL ABJohn
Bookmarks