MySQL performance with update

Question

Which approach is faster in MySQL ? Implementing a binary tree with MySQL and PHP(Codeigniter).

1) When only one query can update a lots of rows (Insertion of a node in tree using Nested Sets )

2) By using procedure and some if else logic and loops , reducing the number of rows-say, the number of rows to be updated in this approach is half than the previous one. (Implementing the tree with adjacency list - going upward till root and updating each root)

Earlier i had asked this question binary-tree-using-php-mysql But didnt get response . Thats why asked this question which somewhat what I have to know atleast.

Please Help Me !

if the 2nd approach updates half row, it should take less time, but if the procedure and loop logic is implemented in php then the time required may be higher. — Uday Sawant
– Uday Sawant, Commented Mar 24, 2012 at 9:46

Your Common Sense · Accepted Answer · 2012-03-24 09:45:24Z

1

Updating the only rows that needs to be updated is apparently faster.

I see no reason though why it cannot be done with one query.

answered Mar 24, 2012 at 9:45

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shahed Khan Over a year ago

could you please help me with this question too ??? stackoverflow.com/questions/9775720/…

Shahed Khan Over a year ago

or some resource , how to assess performance of alternative solutions in MySQl

Your Common Sense Over a year ago

I can't. There is no real life problem in it. While abstract description can be far from the real needs

user319198 · Accepted Answer · 2012-03-24 09:42:53Z

0

UPDATE is much faster when update a lot of rows in single table, it just rewrites table with new data.

answered Mar 24, 2012 at 9:42

user319198

Comments

Guillaume U · Accepted Answer · 2012-03-24 09:44:04Z

0

On my MySQL servers, the second approach is the fastest solution even if you use a transaction with many updates.

answered Mar 24, 2012 at 9:44

Guillaume U

4361 gold badge4 silver badges6 bronze badges

1 Comment

Guillaume U Over a year ago

I don't know exactly why but I think it comes from the index regeneration.

Collectives™ on Stack Overflow

MySQL performance with update

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related