After some problem on the index of a tables defined therefore:
CREATE TABLE `artmain` (
`art_codice` varchar(22) character set latin1 collate latin1_bin NOT
NULL default '',
`art_descr` varchar(60) NOT NULL default '',
`art_suppl` int(8) NOT NULL default '0',
`art_origi` char(1) NOT NULL default '',
`art_codfam` varchar(6) NOT NULL default '',
`art_segnale` varchar(10) NOT NULL default '',
`art_vettura` varchar(10) NOT NULL default '',
`art_qtaconf` float(8,3) NOT NULL default '0.000',
`art_catalogo` varchar(30) NOT NULL default '',
`art_peso` float(5,3) NOT NULL default '0.000',
`art_catego` varchar(4) NOT NULL default '',
`sot_catcodice` varchar(4) NOT NULL default '',
`aggiorna` int(1) NOT NULL default '0',
PRIMARY KEY (`art_codice`,`art_suppl`),
KEY `CATEGORIE` (`art_catego`,`sot_catcodice`,`art_suppl`),
KEY `art_descr` (`art_descr`,`art_suppl`),
KEY `SUPPLIER` (`art_suppl`,`art_codice`),
FULLTEXT KEY `descrizione` (`art_descr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I made a dump of the table and a successive LOAD
DATA to recharge and to reconstruct the table.
The situation after LOAD DATA was:
seattle:/var/lib/mysql/db # ls -l artmain*
-rw-rw-rw- 1 mysql mysql 426799972 Feb 19 10:52 artmain.MYD
-rw-rw-rw- 1 mysql mysql 789188608 Feb 19 12:52 artmain.MYI
-rw-rw-rw- 1 mysql mysql 9078 Feb 19 10:54 artmain.frm
And myisamchk brought back:
seattle:/var/lib/mysql/db # myisamchk -i --verbose artmain
Checking MyISAM file: artmain
Data records: 8204471 Deleted blocks: 0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 65% Packed: 60% Max levels: 5
- check data record references index: 2
Key: 2: Keyblocks used: 87% Packed: 0% Max levels: 5
- check data record references index: 3
Key: 3: Keyblocks used: 56% Packed: 84% Max levels: 5
- check data record references index: 4
Key: 4: Keyblocks used: 62% Packed: 72% Max levels: 5
- check data record references index: 5
Key: 5: Keyblocks used: 51% Packed: 96% Max levels: 5
Total: Keyblocks used: 63% Packed: 87%
- check record links
Records: 8204471 M.recordlength: 47 Packed:
69%
Recordspace used: 98% Empty space: 1% Blocks/Record:
1.00
Record blocks: 8204471 Delete blocks: 0
Record data: 390139470 Deleted data: 0
Lost space: 5950757 Linkdata: 30709745
User time 59.20, System time 57.80
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 812, Physical pagefaults 18, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 318102, Involuntary context switches 2071
I used the database for many hours with massive INSERT, UPDATE and
REPLACE operations and the table's situation was:
-rw-rw---- 1 mysql mysql 426899256 Feb 23 01:02 artmain.MYD
-rw-rw---- 1 mysql mysql 490489856 Feb 23 01:58 artmain.MYI <--- !!!
-rw-rw---- 1 mysql mysql 9078 Feb 17 20:14 artmain.frm
#myisamchk -i --verbose artmain
Checking MyISAM file: artmain
Data records: 8208200 Deleted blocks: 0
- check file-size
- check record delete-chain
No recordlinks
- check key delete-chain
block_size 1024:
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 98% Packed: 60% Max levels: 4
- check data record references index: 2
Key: 2: Keyblocks used: 97% Packed: 0% Max levels: 5
- check data record references index: 3
Key: 3: Keyblocks used: 98% Packed: 84% Max levels: 5
- check data record references index: 4
Key: 4: Keyblocks used: 98% Packed: 72% Max levels: 4
- check data record references index: 5
Key: 5: Keyblocks used: 92% Packed: 96% Max levels: 4
Total: Keyblocks used: 96% Packed: 88%
- check record links
Records: 8208200 M.recordlength: 47 Packed:
69%
Recordspace used: 98% Empty space: 1% Blocks/Record:
1.00
Record blocks: 8208200 Delete blocks: 0
Record data: 390223321 Deleted data: 0
Lost space: 5952093 Linkdata: 30723842
User time 138.63, System time 31.30
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 830, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2220, Involuntary context switches 9079
The final records number is correct and the procedure using the table,
it does not give errors. How it is possibile that the index has become
smaller than about 200MB, if the records are increased?
- Linux version 2.6.11.4-20a-smp ([EMAIL PROTECTED]) (gcc version 3.3.5
20050117 (prerelease) (SUSE Linux))
#1 SMP Wed Mar 23 21:52:37 UTC 2005
- mysql Ver 14.7 Distrib 4.1.10a, for suse-linux (i686)
thanks for the aid
--
Pasquale D'Orsi <[EMAIL PROTECTED]>
Sofinn Italia srl
Direzione Tecnica
"Gli uomini fanno progetti e gli dei sorridono"
(Meir Shalev)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
