Hi!
On Jul 22, William R. Mussatto wrote:
> >
> > Well, on my tests I was able to add ~4,000,000 rows in 17 hrs
> > with MySQL 3.23, and in 4 hrs in MySQL 4.0
> >
> > Table had 4 indexes. Removing those increased the speed by
> > several orders of magnitude (!!!).
> >
> > Hadware was moderate - Athlon 1GHz, 640M RAM.
> >
> Ok, how long did it take afterwards to alter table and put the extra
> indexes back?
About two hours, if I'm not mistaken.
Note, that original table had ~120,000,000 rows.
So adding ~4,000,000 rows and then indexing ALL THE TABLE (~125,000,000 rows)
is MUCH faster than indexing ~4,000,000 on-the-fly (do not forget -
the table had 4 indexes), even when special bulk-insert optimization
is used.
> Isn't there a flag which allows the insert to ignore
> errors (ie., duplicate unique fields). If this is true, couldn't the
> insert be programmed to do the insert and then add the indexes after ALL
> the inerts were loaded? Some kind of bulk load syntax? Just a thought?
Sure. MySQL does this already when inserting data into empty
table. The trick is activated for INSERT ... SELECT, INSERT ... (...),(...),...
and LOAD DATA INFILE.
But MySQL can not guess whether it would be a win to reindex all the table,
if one is adding data to non-empty table. So, one is advised to
disable indexes with myisamchk, and re-enable them again after bulk loading
the data. It's a recommended procedure and is described in the manual
(13.5.7 Speed of INSERT Queries).
In the 4.0 there will be a new command ALTER TABLE ... DISABLE/ENABLE KEYS,
that will allow to do the above from any MySQL client, without resorting to
external command-line utilites.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
