I have a table with ~ 40 mil records, with a unique index on the url column, 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).



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


  • 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:

  `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`)

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

IF(affected_rows>0) {
    .... do some ohter insert using id
}else {
    id = SELECT id FROM url_unique WHERE url = 'http://domain.com';
    .... do some other 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)

  • 1
    table_open_cache = 1 seems like a potential performance killer. – Michael - sqlbot Feb 19 '13 at 22:26
  • oups, I've messed with the .cfg some time before and forgot that there :), should 1000 suffice? – Stefan Rogin Feb 20 '13 at 6:44
  • now it's at about .015 (~60/s) with nothing else running on the server, is there anything else I could do to speed it up, I see that the selects I did last night still run fast now, even after I've restarted the MySQL server, probably some Linux read cache. How much data can it cache? could I do some warm-up process? – Stefan Rogin Feb 20 '13 at 6:48
  • Questions: Is the client running on the same machine as the server, or a different machine? Have you benchmarked with multiple concurrent clients? With an empty table with the same structure? Without the unique key? With an InnoDB table? If you slam the server with repeated inserts, what values peak up on the "Cpu(s)" row of top -- %us, %sy, %wa, %hi, %si, or %st? – Michael - sqlbot Feb 20 '13 at 7:19
  • for these tests I've used a different machine as client, but the main application will be on server. I needed about 10x speed, so I didn't think the networking would matter as most operations are on server. For my data usage I've found that MyISAM is faster, as I don't need high reads as I need inserts. Even with table locks from 500 connections/s it still is faster than InnoDB. Anyway I found that using LOAD INDEX INTO CACHE url_unique did the trick, speed is now at ~ 0.001 (1k/s) from remote. – Stefan Rogin Feb 20 '13 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.