why is this query taking so long?

Question

+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| Id | User        | Host      | db       | Command | Time | State    | Info                                                   |
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| 39 | tigase_user | localhost | tigasedb | Query   | 5406 | updating | delete from tig_pairs where uid >= 2 and uid <= 700000 |
| 40 | tigase_user | localhost | tigasedb | Query   |    0 | NULL     | show processlist                                       |
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+

It just stays in the 'updating' state for most of the 5406 seconds. Mysql version Server version: 5.1.69-0ubuntu0.11.10.1-log (Ubuntu)

thanks

 mysql> select count(*) from tig_pairs;
+----------+
| count(*) |
+----------+
|  6275968 |
+----------+
1 row in set (2.84 sec)

mysql> show create table tig_pairs;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tig_pairs | CREATE TABLE `tig_pairs` (
  `nid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `pkey` varchar(255) NOT NULL,
  `pval` mediumtext,
  KEY `uid` (`uid`),
  KEY `pkey` (`pkey`),
  KEY `nid_pkey` (`nid`,`pkey`),
  CONSTRAINT `tig_pairs_constr_1` FOREIGN KEY (`uid`) REFERENCES `tig_users` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

why is this query taking so long?

0 Answers0