Speeding up mysql inserts with large amount of data

[jamesa]

The table has lots of indexes on it, which definitely slows things down...

Method 1:

mysql> delete from Inventory;
Query OK, 69561 rows affected [b](0.04 sec)[/b]

mysql> LOAD DATA INFILE 'tfout.txt' INTO TABLE Inventory;
Query OK, 69561 rows affected [b](50 min 52.42 sec)[/b]
Records: 69561 Deleted: 0 Skipped: 0 Warnings: 0

Total elapsed time = 50 minutes 52.46 seconds. Ouch.

Method 2:

mysql> delete from Inventory;
Query OK, 69561 rows affected [b](0.05 sec)[/b]

mysql> ALTER TABLE Inventory DISABLE KEYS;
Query OK, 0 rows affected [b](0.01 sec)[/b]

mysql> LOAD DATA INFILE 'tfout.txt' INTO TABLE Inventory;
Query OK, 69561 rows affected [b](2.29 sec)[/b]
Records: 69561 Deleted: 0 Skipped: 0 Warnings: 0

mysql> ALTER TABLE Inventory ENABLE KEYS; 
Query OK, 0 rows affected [b](44.85 sec)[/b]

Total elapsed time = 47.2 seconds. Much better! :)

Time saved using ALTER TABLE .. DISABLE KEYS & ALTER TABLE .. ENABLE KEYS = 50 minutes 5.26 seconds

Time it took me to figure this out = hours.

Hope this helps someone someday.

[dmorison]

Ok, any thoughts on why?

I'm investigating...

[plumsauce]

Because the database does not have to do any
index manipulation during the insert. The
indexes are rebuilt in a single operation
after the inserts are finished.

++++

[dmorison]

Fair enough, i'd kinda assumed it would be doing that anyway, but on retrospect it shouldn't unless told that it's ok, which is what's going on here.

[txbakers]

I have a mySql question that didn't get answered very well on the mySql forums.

I have several tables using an auto_increment field as primary unique key. I use that number to identify unique items in the database.

As items get deleted, the incrementer keeps going, without filling in the deleted numbers.

Is there a way to configure the db to allow it to reclaim the missing numbers?

I can envision the field becoming too big to use within a few years. One use of the field is as the barcode number for the inventory item. Another is a unique entry number on a contest form.

[coopster]

Is there a way to configure the db to allow it to reclaim the missing numbers?

No.

An INSERT to a TABLE with an AUTO_INCREMENT column is going to generate an error once you reach the limit for the numeric value; it won't wrap and start over (obviously that would be bad).

There is a User Contributed note in the MySQL Manual AUTO_INCREMENT [mysql.com] page that reads as follows:

For those that are looking to "reset" the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following.

DROP the field you are auto_incrementing.
ALTER the table to ADD the field again with the same attributes.

You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1.

(Keep in mind that DROPping that column will remove all existing data, so if you have exterior resources that rely on that data, or the numbers that are already there, you may break the link. Also, as with any major structure change, it's a good idea to backup your table BEFORE you make the change.)

**CAUTION**
Anybody considering this -- if you are using an InnoDB table type -- MySQL warns "If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types except InnoDB. See section 7.5.12.5 How an AUTO_INCREMENT Column Works in InnoDB." Therefore I don't know what will happen if you DROP the column altogether.

I don't like this idea anyway. Why not just ALTER your column to Column Type INTEGER? This would give you 4,294,967,295 INSERTS before you would have to get concerned. And if that doesn't satisfy you, go with BIGINT. That would probably be overkill though.

Just some thoughts.

[coopster]

Great tip, by the way jamesa. I've never done things this way though. I usually DROP, CREATE and LOAD.

How fast does the LOAD DATA INFILE run if you simply DROP the table first and CREATE it again?

[jamesa]

>> DROP, CREATE and LOAD

Well if I include the indexes in the create statement we're back to the same problem as the first scenario. But when I tried LOAD DATA INFILE on the same table structure without the indexes it was lightning fast.

[jamesa]

But actually that's a good way to reset the auto_increment.

[coopster]

I am usually using auto_increment to uniquely identify a row in my table which in turn is used as the primary key to relate to other tables. Using the method mentioned would surely do some irreversible damage in that case. If you aren't using the table in this manner, it's probably the best work-around I've seen yet.

[plumsauce]

dmorison,

but on retrospect it shouldn't unless told that it's ok,

well, it was:

mysql> ALTER TABLE Inventory DISABLE KEYS;

:)