Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The table has about 12 rows.

Indexes:

idint(11), Unique, auto-increment, primary

ATTACHMENTIDvarchar(14), Unique

MLSNUMBERbigint(20)

POSITIONint(10)

FILETYPEvarchar(32)

I'm doing an extended insert with an ON DUPLICATE KEY UPDATE clause. Each insert has anywhere from 300-10,000 records. The more records in the table, the longer the query takes.

With 0 rows, the query took about 5-10 seconds. After about 300,000 rows, the query was taking 50 seconds.

What would be the best way to optimize this table? Thanks in advance!

share|improve this question
    
Is the table MyISAM or innodb? –  Derek Downey Jul 26 '12 at 20:57
    
It is InnoDB. _ –  Blenderer Jul 26 '12 at 21:17
add comment

2 Answers

up vote 2 down vote accepted

Since the table is InnoDB

You may have to resort to altering the InnoDB buffer protocol to handle only INSERTs : Mysql load from infile stuck waiting on hard drive

Unless you plan to have store values in MLSNUMBER bigger than 2147483647, you may want to consider making MLSNUMBER an INT (4 bytes) instead of BIGINT (8 bytes) to save space on secondary index creation. If your values for MLSNUMBER are less than 4294967296, maybe MLSNUMBER should be INT UNSIGNED.

share|improve this answer
add comment

The reason it takes progressively longer when your table has a lot of rows is due to having to write the indexes. Try turning offing indexes for the table before inserting, then re-enable afterwards:

ALTER TABLE my_table DISABLE KEYS;

-- Your insert statement

ALTER TABLE my_table ENABLE KEYS;

Disabling keys only affects the updating of non-unique keys, so ON DUPLICATE UPDATE will still work as intended:

ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. [src]

share|improve this answer
    
Won't this make ON DUPLICATE KEY UPDATE not work? –  Blenderer Jul 26 '12 at 21:16
    
Clarified that point...it should still work. –  Derek Downey Jul 26 '12 at 21:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.