CODEWITHSUNDEEP
mysqlsql
Daniel T.
Daniel T.

Reputation: 38410

SQL to insert millions of dummy records into MySQL database

I have a MySQL database that I'd like to test the performance of. I want to insert approximately 5 million records into a table with an auto-incremented primary key. Is there an easy way to do this quickly with SQL? I know I can pipe in a .sql file, but all I really want to do is insert the same identical data (but with a different PK), 5 million times.

Upvotes: 0

Views: 2735

Answers (2)

Andrew Kuklewicz
Andrew Kuklewicz

Reputation: 10701

This may not be a good performance test, as identical rows will not create indicies with regularly distributed values for anything but the PK, so most queries will not demonstrate expected performance.

I would consider using a test data generation tool like Faker (ruby and perl varieties exist):

http://faker.rubyforge.org/

Upvotes: 1

Reza S
Reza S

Reputation: 9748

You can insert NULL as the value of the PK or just simply don't specify it in the columns list in your insert statement. That way it gets auto incremented.

for ($i=0; $i<5000000; $i++) {
    "INSERT INTO table (id,name) values (NULL,'somename')"; // obviously in form of $dbh->() something
}

Upvotes: 2

Related Questions