Updating a table with a single UPDATE statement vs several UPDATE statements

Question

CREATE TABLE `messages` (
  `mes_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'номер объявления',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'дата/время дачи/изменения объявления',
  `expire` date NOT NULL COMMENT 'Дата истечения объявления',
  `paid_top_cat` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху в разделе: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_top_main` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Наверху на главной: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_links` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование ссылок: 0 - нет, 1 - запрошен, 2 - включен',
  `paid_border` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Использование рамки: 0 - нет, 1 - запрошен, 2 - включен',
  `expire_top_cat` date DEFAULT NULL,
  `expire_top_main` date DEFAULT NULL,
  `expire_links` date DEFAULT NULL,
  `expire_border` date DEFAULT NULL,
  PRIMARY KEY (`mes_id`),
  # more fields
) ENGINE=MyISAM ;

I need to update some fields of a table dependently on some fields of the same table. Is the following in an UPDATE a good idea? paid_top_cat=IF(paid_top_cat=2 AND expire_to_cat<NOW(), 1, paid_top_cat), expire_top_cat=IF(expire_top_cat<NOW(), NULL, expire_top_cat) and so on for other expire_* and paid_* fields in a single statement.

What are arguments pro and counter to use this tricky UPDATE statements with many IF functions vs updating every column (or rather a pair of paid_* and expire_* columns) at once, so running UPDATE max 4 times?

MySQL.

Answer 1

When update executed once it's guaranteed that all rows that satisfy conditions will be updated (or none in case of error). 4 separate updates may update different rows (something may change between updates, and you're using MyIsam, so transactions won't help; in addition, your update depends on non-deterministic NOW() ).

Another point is that each UPDATE locks set of rows , so 4 updates will lock rows 4 times even if the same rows are affected(in case of MyISAM)

Answer 2

If your base accumulates rapidly incoming inserts, the good idea is to split long-lasting query into the sequence of small queries. That prevents DB from connections shortage when the connections are wait for the table will be unlocked and new connection being refused and data is being lost.

Then you'll get the execution queue like

<uiiiuiii...

instead of

<Uiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiixxxxxxxxxxx