SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot vbthanks's Avatar
    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.
    THEN I 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 
    Nothing happens to the batch table - it still has overhead and i dislike dreds.
    Cheers,
    Mick
    "You know what you know - but that's all you know!"

  2. #2
    if($awake){code();} PHP John's Avatar
    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 AB
    John


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •