MySQL indexing for INSERT INTO... ON DUPLICATE UPDATE

Question

To give you a context, we have a huge table in our database, with well over 15 million rows.

We are executing a INSERT INTO... ON DUPLICATE KEY query on this table, which is taking more than 20 mins to complete the insert/update.

Example query -

INSERT INTO table1 (date_time, block_start, block_end, tx_id, tz_id, z_id, interval_span,
    interval_id, updated, req, imp, cli)
VALUES ('2018-02-02 15:55:00', '2018-02-02 15:55:00', '2018-02-02 15:59:59', '51530',
    '51530', '8005', '5', '1631', '2018-02-02 15:58:50', '1', '0', '0')
ON DUPLICATE KEY
    UPDATE req = req + 1, imp = imp + 0, cli = cli + 0

Table structure is as below -

CREATE TABLE `table1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date_time` datetime NOT NULL, `interval_span` int(10) unsigned NOT NULL, `interval_id` int(10) unsigned NOT NULL, `block_start` datetime NOT NULL, `block_end` datetime NOT NULL, `tx_id` int(10) unsigned NOT NULL, `tz_id` int(10) unsigned NOT NULL, `z_id` int(10) unsigned NOT NULL, `req` int(10) unsigned NOT NULL DEFAULT '0', `imp` int(10) unsigned NOT NULL DEFAULT '0', `cli` int(10) unsigned NOT NULL DEFAULT '0', `updated` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `iaz_table1` (`block_start`,`tx_id`,`z_id`), KEY `tx_id` (`tx_id`,`date_time`), KEY `z_id` (`z_id`,`date_time`), KEY `date_time` (`date_time`), KEY `block_start` (`block_start`) ) ENGINE=InnoDB AUTO_INCREMENT=257679784 DEFAULT CHARSET=utf8

How can I improve the speed of this insert? I need to achieve execution time of less than 5 seconds.

Are you saying that _one_ IODKU is taking 20 minutes??? Please provide `SHOW CREATE TABLE` so we can see what unique key it is triggering on. — Rick James, Feb 11 '18 at 02:17

score 0 · Answer 1 · answered Feb 11 '18 at 02:18

0

Sounds like you do not have a PRIMARY KEY or UNIQUE KEY included in the list of columns: (date_time, block_start, block_end, tx_id, tz_id, z_id, interval_span, interval_id, updated, req, imp, cli).

answered Feb 11 '18 at 02:18

Rick James

135,179
13
127
222

score 0 · Answer 2 · answered Feb 11 '18 at 07:52

A table definition would be helpful. It looks like all fields are strings, but it seems like many of them could be integers. Integer comparisons are much faster than varchar (integers take up way less space). See this post:

SQL SELECT speed int vs varchar

MySQL indexing for INSERT INTO... ON DUPLICATE UPDATE

2 Answers2