22 秒是在 mysql 中插入 500 行的好时机吗? [英] Is 22 seconds a good time for inserting 500 rows in mysql?

问题描述

以下 php 脚本执行时间大约为 22 秒.正常吗?如果不是什么会导致它执行缓慢?

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

表结构:

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 ;

这段代码只是为了测试插入速度.使用Zend框架(Zend_Db的插入方法)我得到了类似的结果，所以我认为它与mysql相关而不是php代码

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.

推荐答案

22 秒是很长的时间.我的猜测是每次插入后，索引都需要更新.

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");

这篇关于22 秒是在 mysql 中插入 500 行的好时机吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！