+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| 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)
SHOW CREATE TABLE tig_pairs;
) so we know engine used and indexes. And how big is the table, how many rows?uid
each) instead of one bigDELETE
?TRUNCATE
the table. TRUNCATE simply drops everything, DELETE goes row by row, while keeping MVCC guarantees