CODEWITHSUNDEEP
mysqloptimization
dido
dido

Reputation: 2361

How can I optimize MySQL query for update?

I have a table with 300 000 records. In this table have duplicae rows and I want to update column "flag"

TABLE

------------------------------------
|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'

This query working very very slowly (more 600 seconds) for this 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 "name" is duplicate I want to update flag_qty

Upvotes: 6

Views: 8765

Answers (5)

Moshe L
Moshe L

Reputation: 1905

you can try (assuming VB.net, but can be implemented with 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 you get the list of IDs (comma-delimited) than use 

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

It can be slow also, but the first - SELECT, will not lock up your database and replication, etc. the UPDATE will be faster.

Upvotes: 1

user1191247
user1191247

Reputation: 12973

If you do not already have an index on number 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 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

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.

Upvotes: 4

scibuff
scibuff

Reputation: 13765

I would use a temp table. 1.) select all relevant records into a temp table, set INDEX on id. 2.) update the table using something like this

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

Upvotes: 1

kirilloid
kirilloid

Reputation: 14304

EXPLAIN will show you where it is slow and here're some ideas, how to improve perfomance:

  • Add indexing
  • Use InnoDB foreign keys
  • Split query into 2 and process them separately in lagnuage you use.
  • write the same idea in MySQL procedure (not sure, whether this would be fast).

Upvotes: 1

user207421
user207421

Reputation: 311039

Get MySQL to EXPLAIN the query to you. Then you will see what indexing would improve things.

Upvotes: 1

Related Questions