insert dummy data to mysql fast
Question by fxuser
I have 1 function in my debug model which i want to use in order to add dummy data to my app to test its speed and such…
the problem is that it needs to add records to 2 different tables and also check for duplicates usernames etc before each record is added to db so it takes a little time…
also this procedure is repeated about $total different dummy records i want to add at once in a for loop…
for example for 100 new users i want to add it takes around 5 seconds to proceed.
is this time fine or do i need to optimize it?
what if i want to add 1000,10000 users at once?? is it possible?
EDIT:
Function called to insert data:
public function registerRandomUsers($total = 1){
$this->load->model("misc_model");
$this->load->model("encryption_model");
$this->load->model("signup_model");
for ($i=1;$i<=$total;$i++){
$username = $this->misc_model->generateRandomString(15);
$flag = false;
while ($flag == false){
if ($this->user_model->usernameExist($username)){
$username = $this->misc_model->generateRandomString(15);
}else{
$flag = true;
$password = 'Test123';
$email = $username.'@email.com';
$data = array(
'username' => $username,
'password' => $password,
'email' => $email
);
$this->signup_model->submitRegistration($data);
$userdata = $this->user_model->getUserData($username, "username");
}
}
}
}
Answer by Mikey
If you’re not worried about having a random string as the user name, just set the $email = 'user'.$i.'@email.com'; (so you don’t have to worry about collisions). The main reason this will be running slow is because you’re sending a new query to the database on each iteration of the loop – it would be much much faster to generate a bulk insert string like:
INSERT INTO user (email,pass)
VALUES ('user1@email.com','Test123')
, ('user2@email.com','Test123')
, ('user3@email.com','Test123')
, ('user4@email.com','Test123')
, ('user5@email.com','Test123');
This way you can avoid the overhead of tcp traffic from sending 10000 queries to the database and have it do it all in one go.
Answer by Starx
Build your query as this
$conjuctions = str_repeat("('dummy@email.com','test pass'),", 20); // 20 dummy datas
$query = "INSERT INTO user (email,pass) VALUES ".substr($conjunctions,0,str_len($conjuctions).";"
// ^ This is to remove the last comma
