CODEWITHSUNDEEP
mysqlsqlperformanceinsert
Matthias
Matthias

Reputation: 13434

Mysql faster INSERT

Ok, I've got about 175k INSERT statements, pretty big INSERT statements, example:

INSERT INTO `gast` (`ID`,`Identiteitskaartnummer`,`Naam`,`Voornaam`,`Adres`,`Postcode`,`Stad`,`Land`,`Tel`,`Gsm`,`Fax`,`Email`,`Creditcardnummer`) VALUES ('100001','5121-1131-6328-9416','Cameron','Rhoda','Ap #192-1541 Velit Rd.','54398','Catskill','Bermuda','1-321-643-8255','(120) 502-0360','1 48 428 3971-3704','[email protected]','8378-3645-3748-8446');

(Disregard the fact this is in Dutch). All the data is completely random.

I've got to do this about 3 to 4 times. Querying 100k INSERT statements takes about an hour on my PC. Is there any way to do this faster without altering the INSERT statements? I'm using MySQL Workbench. Thanks.

EDIT

I've tried everything suggested so far. Using only one INSERT but multiple VALUES gives me an error that the INSERT statement is too big. Disabling the indexes before inserting doesn't improve performance either.

I've also tried loading an sql file through command line, but that doesn't do anything either...

Insert performance varies from 0.015s to 0.032s per INSERT.

Upvotes: 4

Views: 4785

Answers (4)

Davidacce
Davidacce

Reputation: 235

Put all your data in a csv file and user load data infile FILENAME into table TABLE It wont take more than a minute If you want to do it with insert it is better to use a single insert sending several rows at a time

Upvotes: 1

sav
sav

Reputation: 2150

Another solution would be to save the data to a text file then load the data in using LOAD DATA LOCAL INFILE

Its probably not as nice but it'll be much faster

Upvotes: 1

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

INSERT all of the data into one table - the table you're going to reseed your database with - and then issue an INSERT SELECT statement because it will then execute it as a batch instead of 175K different statements.

Also, when you reseed your database with the INSERT SELECT statement, turn off contraints on the target table ALTER TABLE yourtablename DISABLE KEYS and then turn them back on afterwards ALTER TABLE yourtablename ENABLE KEYS.

I would also personally build a covering index on the seed data table because then it wouldn't have to read a data page.

Upvotes: 2

arnoudhgz
arnoudhgz

Reputation: 1284

Just use one statement

INSERT INTO `gast` 

    (`ID`,`Identiteitskaartnummer`,`Naam`,`Voornaam`,`Adres`,`Postcode`,`Stad`,`Land`,`Tel`,`Gsm`,`Fax`,`Email`,`Creditcardnummer`) 
    VALUES 
    ('100001','5121-1131-6328-9416','Cameron','Rhoda','Ap #192-1541 Velit Rd.','54398','Catskill','Bermuda','1-321-643-8255','(120) 502-0360','1 48 428 3971-3704','[email protected]','8378-3645-3748-8446'),
    ('100002','5121-1131-6328-9416','Cameron','Rhoda','Ap #192-1541 Velit Rd.','54398','Catskill','Bermuda','1-321-643-8255','(120) 502-0360','1 48 428 3971-3704','[email protected]','8378-3645-3748-8446')

etc etc....

Maybe you should do 10000 a time instead of all 175k

Upvotes: 1

Related Questions