How can I optimize this mySQL table that will need to hold 5+ million rows?

Question

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!

Answer 1

Since the table is InnoDB

• Bulk Insert Buffer (bulk_insert_buffer_size) is no good for you because it handles bulk inserts for MyISAM tables only
• DISABLE KEYS / ENABLE KEYS only works for MyISAM. InnoDB handles secondary index processing in the system tablespace ibdata1.
  • InnoDB will throw a warning about this
  • See my post : https://stackoverflow.com/a/9525780/491757
  • See the bug report : http://bugs.mysql.com/bug.php?id=5187

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.

Answer 2

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]