Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.

Those tables only contain a few thousand rows.

Would it be faster to INSERT data into these tables if I would set a primary index (that I don't need otherwise)?

share|improve this question
add comment

2 Answers

I haven't tested this (as my tables usually have at least a primary key) but I expect the difference to depend on the choice of the primary key.

Based on the information in MySQL documentation about InnoDB engine, all InnoDB tables have a clustered index. This is the PRIMARY key of the table and in lack of one, the first UNIQUE index. And in lack of unique keys as well, a hidden column is created (and values are auto-generated for it) and used internally for unique identification and for clustering.

The "key" here is that this hidden column is a 6-byte integer column. So, you have 6 bytes per row overhead when you don't define primary and any unique index in an InnoDB table.

In contrast, if you have a narrower column (like a 1, 2, 3 or 4 byte integer auto-incrementing column) defined as primary or unique index , you should expect an improvement on INSERT efficiency.

If you define an 8-byte integer I'd expect a decrease on efficiency.

If it is a VARCHAR(20) or a wider combination of column, the decrease would probably be worse, depending on the order you provide the PK values. Auto-incrementing values are by definition increasing (so good for a clustered index). If you provide non-increasing values, that will affect the INSERT efficienct negatively.

share|improve this answer
add comment

On the contrary, a table with no indexes would be faster to run INSERTs on than a table with indexes.

However, in the case of InnoDB, there is already an internal index in use called the gen_clust_index. It is also better known as the Clustered Index. It exists for all InnoDB tables, even those with no PRIMARY KEY.

I have written about the gen_clust_index before

Thus, adding a PRIMARY KEY on an InnoDB table with no PRIMARY KEY buys you nothing but another index.

Another headache in this regard is the fact that all lookups must eventually end up in the gen_clust_index, especially with secondary indexes. In fact, loadding data into an InnoDB table with secondary indexes will pile up changes in the Insert Buffer of the System Tablespace (See Pictorial Representation of ibdata1)

share|improve this answer
add comment

Your Answer

 
discard

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.