Hi,
The question: Is uncompressing a myisam table when adding an index the expected behavior?
Here is the story:
I have a very big table so I thought to compress it.
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
