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)?
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
Sep 20, 2011 : Best of MyISAM and InnoDBMar 27, 2013 : Are there implicit indexes in InnoDB like MyISAM?Feb 24, 2011 : what is gen_clust_index used for in mysql? (ServerFault)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)
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.
