I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255).

Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk insert (insert delayed or more values at a time).

Specs:

Hardware

• 16GB RAM
• intel i7 3.4 Ghz (8core)
• 2 standard 7200rpm HDD (1x 1TB + 1x 2TB)

Software

• MySQL 5.5 (innodb_version 1.1.8, protocol_version 10, version 5.5.29-0ubuntu0.12.10.1-log, version_compile_machine x86_64, version_compile_os debian-linux-gnu)
• Ubuntu 12.10 (debian/linux OS)
• my.cnf
• I'm using the c++ conector as client

Query usage:

CREATE TABLE IF NOT EXISTS `url_unique` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `valid` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY unique_index (`url`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=ascii 
  DEFAULT COLLATE=ascii_bin
  ;


INSERT IGNORE INTO url_unique (url) VALUES ('http://domain.com')

IF(affected_rows>0) {
    id = SELECT LAST_INSERT_ID();
    .... do some ohter insert using id
}else {
    id = SELECT id FROM url_unique WHERE url = 'http://domain.com';
    .... do some ohter insert using id
}

query statistics :

insert ignore into - 0.035s (duplicate value, no insert, affected_rows=0)

update: 0.0328340 (99.588%)
...
Com_insert 1
Handler_write 1
Key_read_requests 34
Key_reads 8
Key_write_requests 2
Key_writes 1
Questions 1
Table_locks_immediate 1

SELECT - 0.002s
(faster because of the insert, some sort of query cache hit probably)