Re: The difference between 1.9G and 2.2G table when create Index

[James Yang]

Thanks guys,

Actaully my Hard disk is 10G, just for one table. There is enough table 
space. I noticed when I created index for 1.9G table, at the first phase, 
mysql copy table, but mysql doesn't write to the tmp index file, I means the 
filesize of the tmp index didn't increase. When I create index for 2.2G 
table, the tmp index file is increased at the first phase. Any reason?

James

james@stripped


> > > I have a MYISAM table, which is about 2.2G and 19,000,000 records. 
>When I
> > > create indexs for it, I couldn't finish after 4 hours.
> >
> > You mean it was still running? Was there still disk or CPU activity
> > after 4 hours?
> >
> > >                                                        But when I 
>reduce
> > > the records to 16,000,000, about 1.9G, the same index can be finished
> > > within 35 minutes. Can anyone explain it? Thanks in advance.
> > >
> >
> > That's much shorter than 4 hours!
> >
> > Maybe your disk was full when creating the 2.2G table's index? MySQL
> > waits until disk space becomes available again. You wouldn't see much
> > disk activity while it is waiting.
> >
> > Keep in mind that MySQL makes a copy of your table while it is adding
> > the index. You'll need twice the space of your initial table, plus
> > space for the additional index.
> >
>
>Check this URL for details:
>
>http://www.mysql.com/doc/en/Full_disk.html
>
>There seems to be an exception to this rule when an index is added, so I
>may be wrong on this. The adding of an index happens in two phases and
>the docs are not clear to me about the first (copying) phase however.
>Your earlier comment indicates it is in the first phase still.
>
> > > I am using PIII 550, RedHat 8.0, with 1G Memory and IDE Hard Disk.The
> > > database is stored at "ext3" file system. When I use "show 
>processlist" for
> > > the 2.2G indexs, it tell me "Copying to tmp table".
>
>
>Regards,
>
>Fred.
>
>--
>Fred van Engen                              XB Networks B.V.
>email: fred.van.engen@stripped                Televisieweg 2
>tel: [phone]                         1322 AC  Almere
>fax: [phone]                         The Netherlands
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    
>http://lists.mysql.com/mysql?unsub=1
>

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus