Table tjrs aussi lourde(en Mo) malgre bcp de suppressions...Résolu

Question

Bonjour, 

je voudrais savoir pourquoi la taille de ma table est toujours aussi grande (55Mo) alors que je viens de supprimer beaucoup d' enregistrements de cette table.... 
(avant: 192 000 => après: 33 000) 

Y a t-il un journal de transaction aussi en mysql? 
si oui, faut-il faire une commande spécial qui le purgerait.. 

si quelqu'un pouvait m'éclairer.. 

merci! 

florent.

blinkseb · Accepted Answer

Salut !

J'ai trouvé ça ça peut peut être t'interesser :


OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it's not likely that you need to do this more than once a week or month and only on certain tables.

For the moment, OPTIMIZE TABLE works only on MyISAM, BDB and InnoDB tables. For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It was also the case for InnoDB tables before MySQL 4.1.3; starting from this version it is mapped to ALTER TABLE. See section 14.5.2.1 ANALYZE TABLE Syntax.

You can get OPTIMIZE TABLE to work on other table types by starting mysqld with the --skip-new or --safe-mode option; in this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

OPTIMIZE TABLE works as follows:

   1. If the table has deleted or split rows, repair the table.
   2. If the index pages are not sorted, sort them.
   3. If the statistics are not up to date (and the repair couldn't be done by sorting the index), update them. 

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

Before MySQL 4.1.1, OPTIMIZE TABLE statements are not written to the binary log. As of MySQL 4.1.1, they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is used. 


Voilà en espérant t'avoir été utile !

bLiNkSeB - Anciennement sebastienbro

flashfun · Answer

Sinon, utilise phpmyadmin, OPTIMIZE TABLE y est intégré.

FlashFun  8-)   [mailto:toujours_zen@hotmail.com  :question) ]

floben21 · Answer

Exact!

j'avais essayé FLUSH TABLE mais ça n'a rien donné.

Par contre:

OPTIMIZE TABLE nomDeLaTable 
nettoie bien la table.

Merci.

floben21

Table tjrs aussi lourde(en Mo) malgre bcp de suppressions...

3 réponses

Votre réponse

Discussions similaires