Slow inserts on database due to primary key

Question

Got a MySQL database with a table consisting values based on zipcode and house number. Primary key therefore is chosen to be zipcode and house number as these are the fields to search for. The database contains approximately 10 million records.

One specific zipcode consists of ~100000 different house numbers and extremely slow on inserting (1 hour per 10000 records).

Programming language is Java and I'm using prepared statements in a batch of 10000 with autocommit on false.

The structure of the table is the following:

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| zipcode         | varchar(6)  | NO   | PRI | NULL    |       |
| house_no        | int(11)     | NO   | PRI | NULL    |       |
| sanddcode       | varchar(45) | NO   |     | NULL    |       |
| depot           | varchar(3)  | NO   |     | NULL    |       |
| network_point   | varchar(6)  | NO   |     | NULL    |       |
| region          | varchar(3)  | NO   |     | NULL    |       |
| seq             | int(11)     | NO   |     | NULL    |       |
| cluster_id      | varchar(1)  | NO   |     | NULL    |       |
| strand_id       | int(11)     | NO   |     | NULL    |       |
| strand_props_id | int(11)     | NO   |     | NULL    |       |
| version_id      | int(11)     | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Anyone who knows a solution to this? Thanks!

Zicode and house number is likely not a good primary key in that case. The reason is that the primary key is unique. That means when zipcode and house number are the primary key, there can be only one house with each number in each zip code. Are you aware of that? — Philipp
@Philipp, I think the OP has a composite key, i.e. a key made up of multiple fields. In this case I don't see how that is a problem? I would still recommend a surrogate key instead though. — KingCronus
@KingCronus It is definitely a problem because one ZIP contains more than one street. — Marko Topolnik
Composite primary key is not a very good idea, anyway. Use a sequence number for that and think later about enforcing any uniqueness constraints. — Marko Topolnik
@MarkoTopolnik - Wow...goes to show I need an extra coffee today, of course you are right! I was not considering that case, in the UK (where I am located) I think there is a different code per street. — KingCronus

Arjan Arjan · Accepted Answer · 2012-12-14T14:57:41

When you need to insert a lot of rows, it's probably faster to first remove the keys, then insert all the data, and then re-create the keys. That way the database does not need to spend a lot of time updating those keys.

Slow inserts on database due to primary key

2 Answers