Hi,

The question: Is uncompressing a myisam table when adding an index the expected behavior?

Here is the story:

I used myisampack which worked great. The table.MYD shrank in size.

myisamchk -ddv shows the table as compressed.

I noticed the documentation that urges to run:

myisamchk -rq

after myisampack and I thought: Maybe I shouldn't create any indices on the uncompressed table since myisamchk will redo the work.

But if I try to add an index to the compressed table suddenly it gets expanded to its original size and myisamchk -ddv does report the table a uncompressed.

So the only solution is to:

1.  add the indices in the uncompressed table
2. compress the table
3. run myisamchk -rq [--sort-index --analyze --parallel-recover  --tmpdir=/somewhere/with/a/lot/of/space]
4. mysqladmin flush-tables;

But this duplicates the index creations step.

So is this the expected behavior?

• If yes: why? Expanding the table behind the user's back is surprising...
• If no: Should I report it as a bug?
  

Here is how to replicate:

#DROP TABLE IF EXISTS txxx;
CREATE TABLE txxx (id INT, value TEXT);
INSERT INTO txxx SELECT 1, REPEAT('A', 65536);

# Let's go to shell
ls -ltr txxx.*
-rw-rw---- 1 mysql mysql   466 Jun 30 19:34 txxx.frm
-rw-rw---- 1 mysql mysql  1024 Jun 30 19:40 txxx.MYI
-rw-rw---- 1 mysql mysql 65548 Jun 30 19:40 txxx.MYD   <-- 64K cool

# cool! let's compress
myisampack -f txxx

Compressing txxx.MYD: (1 records)
- Calculating statistics
- Compressing file
87.42%     

ls -ltr txxx.*
-rw-rw---- 1 mysql mysql  466 Jun 30 19:34 txxx.frm
-rw-rw---- 1 mysql mysql 8255 Jun 30 19:40 txxx.MYD  <-- 8K cool
-rw-rw---- 1 mysql mysql 1024 Jun 30 19:41 txxx.MYI
# great it is compressed

# because documentation says so:
myisamchk -qr txxx
- check record delete-chain
- recovering (with keycache) MyISAM-table 'txxx'
Data records: 1

ls -ltr txxx.*
-rw-rw---- 1 mysql mysql  466 Jun 30 19:34 txxx.frm
-rw-rw---- 1 mysql mysql 8255 Jun 30 19:40 txxx.MYD  <-- Still compressed
-rw-rw---- 1 mysql mysql 1024 Jun 30 19:43 txxx.MYI

myisamchk -ddv txxx

MyISAM file:         txxx
Record format:       Compressed <-------------------------------------!!!! OK
Character set:       utf8mb3_general_ci (33)
File-version:        1
Creation time:       2024-06-30 19:40:36
Recover time:        2024-06-30 19:43:52
Status:              checked
Checksum:               2613455662
Data records:                    1  Deleted blocks:                 0
Datafile parts:                  1  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        3
Datafile length:              8248  Keyfile length:              1024
Max datafile length: 281474976710654  Max keyfile length:   17179868159
Recordlength:                   15

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
ro

# Now Let's add the index

ALTER TABLE txxx ADD INDEX(id);

# Shell again
ls -ltr txxx.*
-rw-rw---- 1 mysql mysql   958 Jun 30 19:47 txxx.frm
-rw-rw---- 1 mysql mysql 65548 Jun 30 19:47 txxx.MYD <--- Oups - now it is uncompressed
-rw-rw---- 1 mysql mysql  2048 Jun 30 19:47 txxx.MYI

myisamchk -ddv txxx

MyISAM file:         txxx
Record format:       Packed <----------------------------- Why?
Character set:       utf8mb3_general_ci (33)
File-version:        1
Creation time:       2024-06-30 19:47:30
Status:              changed
Data records:                    1  Deleted blocks:                 0
Datafile parts:                  1  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:             65548  Keyfile length:              2048
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                   16

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     4   multip. long NULL                      0         1024       1024