Is 22 seconds a good time for inserting 500 rows in mysql?

Question

The following php script takes about 22 seconds to execute. Is it normal? If not what would cause it to execute slowly?

$conn = mysql_connect('localhost', 'root', '123');
mysql_select_db('mydb', $conn);
$time1 = time();
for ($i=1;$i<500;$i++) {
mysql_query("Insert into accounts(id, username, email, password) VALUES(\"$i\", \"$i\",\"$i\",NOW())");
}
print time() - $time1; // return ~22

Edit: Table structure:

CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `email` varchar(200) NOT NULL,
  `password` varchar(20) NOT NULL,
  `status` varchar(10) DEFAULT 'pending',
  `email_newsletter_status` varchar(3) DEFAULT 'out',
  `email_type` varchar(4) DEFAULT 'text',
  `email_favorite_artists_status` varchar(3) DEFAULT 'out',
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7500 ;

This code is just to test insert speed. Using the Zend framework(insert method of Zend_Db) I get a similar result, so I think it's relevant to mysql and not php code

Edit2: I know that there is much better ways to execute this query, but I want to know why this one is so slow.

Depends on a lot of factors. Is your table indexed? Does it have foreign key constraints? As for your real question, is 22 seconds too slow: You tell us. Is it too slow for your specific application and user base? — Chris Laplante, Aug 15 '12 at 20:46
Wouldn't executing all the inserts into a single transaction make it faster? I don't remember off the top of my head if it does in mysql. — CBredlow, Aug 15 '12 at 20:47
You've provided nothing about your table structure or configuration. We have no idea why your database is performing slowly. — Brad, Aug 15 '12 at 20:47
It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this article](http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/). — Matt, Aug 15 '12 at 20:48

score 8 · Accepted Answer · answered Aug 15 '12 at 20:48

8

22 seconds is a very long time. My guess is that after each insert, the INDEXes need to be updated.

Try doing this as a transaction instead.

mysql_query("START TRANSACTION");
for ($i=1;$i<500;$i++) {
    mysql_query("Insert into accounts(id, username, email, password) VALUES(\"$i\", \"$i\",\"$i\",NOW())");
}
mysql_query("COMMIT");

answered Aug 15 '12 at 20:48

gen_Eric

223,194
41
299
337

2

Why not concatenate the `values` into a string for a single insert? – Matt Aug 15 '12 at 20:49
@Matt: That's a good idea too. Just figured I'd give an alternate suggestion, though. – gen_Eric Aug 15 '12 at 20:51
@Matt: Also I don't know if having 500 VALUEs in one query is too much. I remember I was working on a project, and I had to break large INSERTs into chunks because I kept getting errors when trying to run the SQL. – gen_Eric Aug 15 '12 at 20:53
If the server is misconfigured with a very small `max_allowed_packet` setting it will reject queries that are "too long". A properly configured server will allow a minimum of 1MB, where a more generous limit is 1GB. You can always check what this is by looking it up in `SHOW VARIABLES`. – tadman Aug 15 '12 at 20:57
Thanks, your solution decreases the execution time to less than 1 second, but does my code actually need 20 seconds to execute? – user16948 Aug 15 '12 at 20:59
@user16948: Not at all. 20 seconds is an *extremely* long time. – gen_Eric Aug 15 '12 at 21:00
I use Zend Framework and it`s built-in PDO for querying DB. Adding transactions in my case (innoDB table with many indexes) resulted as follows: Long task took 54 minutes instead of 14 hours 13 minutes. Transactions are is worth considering, especially when you have many indexes on table. – denesis Sep 11 '20 at 08:08

Craig Godden-Payne · Answer 2 · 2012-08-15T20:50:40.120

6

Rather than run multiple insert statements, batch them up and execute it as one statement

 INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

This will speed up your insert considerably

edited Aug 15 '12 at 20:50

answered Aug 15 '12 at 20:48

Craig Godden-Payne

235
1
4

I know this...I want to know why my code takes so much time to execute – user16948 Aug 15 '12 at 21:08

score 2 · Answer 3 · answered Aug 15 '12 at 20:54

If you're using a stock my.cnf your performance may be terrible. You may want to look at how your MySQL is configured and adjust parameters accordingly. Tuning InnoDB, which is the better engine, requires adjusting different values than MyISAM which is often the fast-but-unsafe default.

If these queries are taking measurable amounts of time to execute, try watching with the MySQL administrator tool or execute SHOW PROCESSLIST frequently to see what the engine is doing.

Inserting 500 records through the MySQL driver directly should take, at most, only a few seconds on such a simple table.

score 0 · Answer 4 · edited Jun 19 '19 at 07:17

0

Try a single insert query:

$valStr = "";
for ($i=1;$i<500;$i++) {
    $valStr = $valStr . ($valStr == "" ? "" : ", ") . "('$i', '$i', '$i', NOW())";
}
if ($valStr != "") {
    mysql_query("Insert into accounts(id, username, email, password) VALUES $valStr");
}

Also, you should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this article.

edited Jun 19 '19 at 07:17

Kalesh Kaladharan

1,038
1
8
26

answered Aug 15 '12 at 20:51

Matt

6,993
4
29
50

2

What's with the `mysql_query` in your example? I thought you were on Team Deprecation. – tadman Aug 15 '12 at 20:52
I just cry a bit when I see all this hazardous SQL being copy-pasted around like it's the cool thing to do. Might be better to avoid string substitutions in this example by using something like `UUID()` for placeholder data. – tadman Aug 15 '12 at 20:56
@tadman I just wanted to get a quick alternative solution in to OP that would be valid, based on the code he provided. – Matt Aug 15 '12 at 20:58

Is 22 seconds a good time for inserting 500 rows in mysql?

4 Answers4

Linked