All geek questions in one place

MySQL: UPDATE optimization of many rows in a large table

The table is organized using a nested dialing model . When I insert something, I need to move everything with left / right value> assignment to the left.

UPDATE projects SET rgt = rgt + 2 WHERE rgt >= @superRgt;

This request may take a few seconds, which is unacceptable. My question is: how to optimize this query? it is possible ..

  • Normalize / defragment / reorder the physical location of the table?
  • Create better indexes?
  • Just smarter and avoid problems?

We use the Innodb table and already have indexes on the left, right and left / right. The table has ~ 100 thousand rows.

+3
source share
2 answers

No magic bullets, but a few thoughts ...

, , , MySQL ( ) .

, - , . , left , left/right. , , , , left right, , ( left/right, , ), , MySQL , .

, , , / ? "" - , .

, , MySQL . ( ~ 30%), MySQL . , - , , , > 30% .

, () innodb ? . . , , innodb_buffer_pool_size ( ) innodb_flush_log_at_trx_commit 0 2, .

+1

, ENGINE = MEMORY, .

0

Source: https://habr.com/ru/post/1785918/

More articles:


All Articles