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
  •