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
[EMAIL PROTECTED]
> > 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: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
