mysql is taking too long to insert rows [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

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 bu开发者_开发技巧t no success there either.

---

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?

---

In addition to the suggestions @Wouter provided, you should check your indexes. If you are inserting 10k+ rows, you may want to disable keys prior to the insert process. Don't forget to enable keys afterward.

ref. MySql 5.5 Documentation:

This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege...