List:Internals« Previous MessageNext Message »
From:Paul DuBois Date:October 22 2002 5:43pm
Subject:Re: MySQL 4.0.4 Pro - DELETE FROM tablename does not clear index
View as plain text  
At 14:13 +0300 10/22/02, Alexander Keremidarski wrote:
>Hello,
>Nicholas Gaugler wrote:
>>mysql-4.0.4-beta-pc-linux-gnu-i686.tar.gz
>>
>>mysql> select version();
>>+------------+
>>| version()  |
>>+------------+
>>| 4.0.4-beta |
>>+------------+
>>1 row in set (0.02 sec)
>
>I could answer you 'Go and read manual' :) but I stopped and checked 
>your question very thoroughly and I have to admit there is something 
>strange from DBA point of view.
>
>>When running DELETE FROM tablename;  OR DELETE FROM tablename WHERE 1=1;
>>MySQL will reset the .MYD file, but will not reset the .MYI file.  Scenario:
>
>Yes that is true. .MYI file will occupy same space untill you run 
>OPTIMIZE or myisamchk -r
>
>Reason is that space in .MYI can be reused.
>
>>I know truncate and delete from are now two seperate functions in 4.x,
>>however I did not see anything in the documentation about an index not being
>>reset after a DELETE FROM.  I also ran a DELETE FROM t1 WHERE 1 = 1; and it
>>did the same thing.  If someone deletes rows from a MyISAM table, shouldn't
>>the indexes be removed as well?
>
>You are right. Above change is not well documented. Manual doesn't 
>mention that DELETE was also changed in 4.x not only TRUNCATE. In 
>4.* DELETE from table, deletes rows, one by one. At the same time it 
>manual also doesn't mention that space in .MYI can be reused too.
>
>I will contact our documentation team about this case. This change 
>should be better docummented. I don't thing it can be assumed as 
>BUG, but still looks unnatrural so I'd like to dicsuss it also 
>within Developer Team.

Yes, ask the developers first.  It's probably not documented well, because
it's impossible to figure out!  The behavior of DELETE with and without a
WHERE clause, and the behavior of TRUNCATE are very difficult to characterize
precisely.  For example, I'd like to know what the rules are for knowing
whether or not DELETE with no WHERE clause will return "0 rows" or a true
row count, and I'd like to know the same for TRUNCATE.  It varies depending
on MySQL version and table type.

More bizarre yet, DELETE FROM tbl_type for a MyISAM table will return
*either 0 rows or a true row count* in some versions of MySQL, and the
determining factor is ... whether or not the InnoDB handler is enabled!

Ugh.

>
>Meanwhile keep in mind that if you need to repeat 3.23 behaviour of 
>DELETE clause you need OPTIMIZE after.
>
>>Nickg
>>
>
>Best regards
>
>--
>  For technical support contracts, visit https://order.mysql.com/
>     __  ___     ___ ____  __
>    /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <salle@stripped>
>   / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
>  /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
>          <___/   www.mysql.com   M: +359 88 231668

Thread
MySQL 4.0.4 Pro - DELETE FROM tablename does not clear indexNicholas Gaugler6 Oct
  • Re: MySQL 4.0.4 Pro - DELETE FROM tablename does not clear indexAlexander Keremidarski22 Oct
    • Re: MySQL 4.0.4 Pro - DELETE FROM tablename does not clear indexPaul DuBois22 Oct