Slow insertion speed on MYISAM table

I need to create a table containing two attributes: id and author. These two attributes form the composite key. Moreover I need to perform an index search on the author field. Therefore I create the table using the following statement:

CREATE TABLE IF NOT EXISTS authors (author VARCHAR(100) NOT NULL, id VARCHAR(200) NOT NULL, INDEX USING BTREE(author,id), PRIMARY KEY (author,id)) ENGINE=MYISAM;

Now, I when try to insert about 4.5 million records using JDBC, the insertion speed gets terribly slow at the end. The id attribute refers to a publication which was created by the related author. One author is related to several ids and vice versa. The average number of identical id values is lower than the number of identical author values. Therefore开发者_运维问答 I tested the same procedure with swapped attributes. In this case, the insertion speed remains nearly constant. Is there a way to optimize the table in order to gain performance? I don't quite know how MYISAM manages indexing composite keys. May be the process of balancing is the reason...

Thanks in advance!

I notice a few problems:

• you're defining two indexes on the same couple of columns (author, id): a normal index and a primary key which is also a special type of unique index.
• the indexes are on very long VARCHAR values.
• your database is not in first normal form, because as you said the author can be repeated, you're using the full author name to create a relationship, while you should use an id and put authors in a separate table.

After these changes, your index will be on simple numeric types and your insert speed should be good.