mysql is taking too long to insert rows [closed]

Question

I have one page where users can import their contacts. Initially it was working fine upto 3000 contacts but when I tried to import 10000 contacts it started taking too much time and now the situation is that for even 100 contacts it is taking too much time. I tried in mysql my.cnf file and increased the max packet size to 256 mb. My php.ini max upload limit is 512 mb, memory limit is 512 mb. I tried several methods to solve this problem but no success.

my.cnf:

[mysqld]
set-variable = max_connections=500000
log-slow-queries
safe-show-database
local-infile=0
max_allowed_packet=256M

I also tried to increase buffer limit, cache limit but no success there either.

Answer 1

Don't automatically assume that your server settings are wrong. The default settings are probably fine. Inserting 10000 rows should be a piece of cake, even on an old machine, but it depends on how you do your inserts.

Here I'll describe 3 methods for inserting data, ranging from slow to fast:

The following is extremely slow if you have many rows to insert:

INSERT INTO mytable (id,name) VALUES (1,'Wouter');
INSERT INTO mytable (id,name) VALUES (2,'Wouter');
INSERT INTO mytable (id,name) VALUES (3,'Wouter');

This is already a lot faster:

INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');

(Edited wrong syntax)

And this is usually the fastest:

Have CSV file that looks like this:

1,Wouter
2,Wouter
3,Wouter

And then run something like

LOAD DATA FROM INFILE 'c:/temp.csv' INTO TABLE mytable

Which of the above methods do you use?