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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The table has about 12 rows.


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

ATTACHMENTIDvarchar(14), Unique




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
up vote 3 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

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:


-- Your insert statement


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 should still work. – Derek Downey Jul 26 '12 at 21:26

Your Answer


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.