3

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!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Blenderer
  • 185
  • 1
  • 8

2 Answers2

4

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]

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
3

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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536