如何优化MySQL查询以进行更新? [英] How can I optimize MySQL query for update?
问题描述
我有一个包含30万条记录的表.在此表中有duplicae行,我想更新列"flag"
I have a table with 300 000 records. In this table have duplicae rows and I want to update column "flag"
表
------------------------------------
|number | flag | ... more column ...|
------------------------------------
|ABCD | 0 | ...................|
|ABCD | 0 | ...................|
|ABCD | 0 | ...................|
|BCDE | 0 | ...................|
|BCDE | 0 | ...................|
我使用此查询来更新标志"列:
I use this query for updating "flag" column:
UPDATE table i
INNER JOIN (SELECT number FROM table
GROUP BY number HAVING count(number) > 1 ) i2
ON i.number = i2.number
SET i.flag = '1'
对于这300,000条记录,此查询的工作非常缓慢(超过600秒).
This query working very very slowly (more 600 seconds) for this 300 000 records.
如何优化此查询?
我的桌子的结构
CREATE TABLE IF NOT EXISTS `inv` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pn` varchar(10) NOT NULL COMMENT 'Part Number',
`qty` int(5) NOT NULL,
`qty_old` int(5) NOT NULL,
`flag_qty` tinyint(1) NOT NULL,
`name` varchar(60) NOT NULL,
`vid` int(11) NOT NULL ,
`flag_d` tinyint(1) NOT NULL ,
`flag_u` tinyint(1) NOT NULL ,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `pn` (`pn`),
KEY `name` (`name`),
KEY `vid` (`vid`),
KEY `pn_2` (`pn`),
KEY `flag_qty` (`flag_qty`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
如果名称"重复,我想更新flag_qty
If "name" is duplicate I want to update flag_qty
推荐答案
如果您尚未在number
上建立索引,则应添加一个-
If you do not already have an index on number
you should add one -
CREATE INDEX table_number ON table (number);
更新,请尝试--
UPDATE inv t1
INNER JOIN inv t2
ON t1.name = t2.name
AND t1.id <> t2.id
SET t1.flag_qty = 1;
您可以直接将数据选择到另一个表中,而不用先进行此标志更新,从而仅使用重复项来创建表.
You can create your table with just the duplicates by selecting this data directly into another table instead of doing this flag update first.
INSERT INTO duplicate_invs
SELECT DISTINCT inv1.*
FROM inv AS inv1
INNER JOIN inv AS inv2
ON inv1.name = inv2.name
AND inv1.id < inv2.id
如果您可以解释从inv
表中删除行的逻辑,则可能是整个过程可以一步完成.
If you can explain the logic for which rows get deleted from inv
table it may be that the whole process can be done in one step.
这篇关于如何优化MySQL查询以进行更新?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!