How can I optimize a MySQL query for an update?

Question

I have a table with 300,000 records. There are duplicae rows in this table, and I want to update the "flag" column

Table

------------------------------------ |number | flag | ... more column ...| ------------------------------------ |ABCD | 0 | ...................| |ABCD | 0 | ...................| |ABCD | 0 | ...................| |BCDE | 0 | ...................| |BCDE | 0 | ...................| 

I use this query to update the 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' 

This query is very slow (over 600 seconds) for these 300,000 records.

How can I optimize this query?

STRUCTURE OF MY TABLE

 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 ; 

If the "name" is duplicated, I want to update the qty_ flag

Answer 1

If you don't have an index on number yet, you should add one -

 CREATE INDEX table_number ON table (number); 

UPDATE Try this -

 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 duplicates only by selecting this data directly in another table, instead of updating this flag 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 

If you can explain the logic from which rows will be deleted from the inv table, maybe the whole process can be completed in one step.

Answer 2

Get MySQL in EXPLAIN request to you. You will then see which indexing improves the situation.

Answer 3

EXPLAIN will show you where it is slow, and here are some ideas on how to improve performance:

• Add Indexing
• Use InnoDB Foreign Keys
• Divide the request by 2 and process them separately in your lagnuage.
• write the same idea in a MySQL procedure (not sure if it will be fast).

Answer 4

I would use a temporary table. 1.) select all the relevant entries in the temporary table, set INDEX to id. 2.) refresh the table using something like this

 UPDATE table i, tmp_i SET i.flag = '1' WHERE i.id = tmp_i.id 

Answer 5

you can try (assuming VB.net, but can be implemented in any language).

 Dim ids As String = Cmd.ExectueScalar("select group_concat(number) from (SELECT number FROM table GROUP BY number HAVING count(number) > 1)") 

After getting a list of identifiers (comma-delimited) than using

 UPDATE i SET i.flag = '1' WHERE i.number in ( .... ) 

It may also be slow, but the first is SELECT , it will not block your database and replication, etc. UPDATE will be faster.