From: Michael Widenius
Date: July 7 1999 9:31am
Subject: Re: MySQL 3.23 & autoincrement
List-Archive: http://lists.mysql.com/mysql/6727
Message-Id: <14211.7157.856603.887311@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Paul" == Paul DuBois <paul@stripped> writes:

Paul> At 7:33 PM +0200 7/5/1999, Martin Ramsch wrote:
>> On Mo, 1999-07-05 12:13:35 -0500, Paul DuBois wrote:
>>> By the way, the no-reuse property doesn't seem to be implemented yet.
>>> Try this:
>>> 
>>> USE test
>>> DROP TABLE IF EXISTS t;
>>> CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY);
>>> INSERT t VALUES(NULL),(NULL);
>>> SELECT * FROM t;
>>> DELETE FROM t;
>>> INSERT t VALUES(NULL),(NULL);
>>> SELECT * FROM t;
>>> 
>>> I get the same result from both SELECT statements:
>> 
>> Please, would you try "DELETE from t where i>0" instead of "DELETE
>> from t"?  If I remember correctly the latter actually drops and
>> recreates the whole table instead of just deleting the records.

Paul> Ah, that makes a difference:

Paul> +---+
Paul> | i |
Paul> +---+
Paul> | 1 |
Paul> | 2 |
Paul> +---+
Paul> +---+
Paul> | i |
Paul> +---+
Paul> | 3 |
Paul> | 4 |
Paul> +---+

Paul> However, DELETE FROM t doesn't actually drop and recreate the
Paul> whole table; it recreates the data and index files, but leaves the
Paul> *.frm file alone.  I'm not sure where the max AUTO_INCREMENT value
Paul> is being stored, but from a logical standpoint, I would expect
Paul> the value to keep on incrementing whether or not I used WHERE 1>0 to
Paul> delete records.

Paul> Monty, any comment?

Hi!

Some comments:

- The .frm file is not touced after you have created the file.
  (If you don't change the table structure with something like ALTER
  TABLE).
- The last used AUTO_INCREMENT value is stored in the .ISM file.
  (Try myisamchk -dv table_name)
- When you do a DELETE from table;
  MySQL will not consult the database files at all, but simply remove
  the old files and recreate them.  This is much faster and will also
  fix things if  by some change there was something wrong in the old
  data files.

I know this isn't 100 % right logically, but I still like the DELETE
optimization...  I shall add to my TODO to look how I can fix this
nicely in the future...

Regards,
Monty