Geek asks and answers

Mysql takes too long to insert rows

I have one page on which users can import their contacts. Initially, it worked fine up to 3,000 contacts, but when I tried to import 10,000 contacts, it started too long, and now the situation is that even 100 contacts take too much time. I tried in the file mysql my.cnfand increased the maximum batch size to 256 MB. The maximum download limit php.iniis 512 MB, the memory limit is 512 MB. I tried several methods to solve this problem, but was not successful.

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 the buffer limit, cache limit, but did not succeed.

+3
source share
2 answers

, . , , . 10000 , , , .

3 , :

, :

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

:

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

( )

, , :

CSV, :

1,Wouter
2,Wouter
3,Wouter

-

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

?

+14

@Wouter, . 10k +, . .

. MySql 5.5:

MyISAM. ALTER TABLE... DISABLE KEYS MySQL, . ALTER ... ENABLE KEYS . MySQL , , , . ALTER TABLE... DISABLE KEYS , INDEX...

+6

More articles:


All Articles