How to speed up Update Mysql queries of 25 million rows?

Question

MySQL

There is a table where pageviews are stored

CREATE TABLE `hi_hits` (
  `id` int(10) UNSIGNED NOT NULL,
  `user` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `url` varchar(255) DEFAULT NULL,
  `hits` mediumint(5) UNSIGNED NOT NULL DEFAULT 0,
  `calls` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
  `referer` varchar(255) DEFAULT NULL,
  `ip` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `date_added` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (`ip`)
PARTITIONS 100;

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `hi_hits`
--
ALTER TABLE `hi_hits`
  ADD PRIMARY KEY (`id`,`ip`) USING BTREE,
  ADD KEY `url` (`url`),
  ADD KEY `date_added` (`date_added`) USING BTREE;

Update is terribly slow from time to time, sometimes it takes up to 20 seconds, the MariaDB 10.4 database itself is on the Samsung SM951 SSD.

Can anyone tell me how to get out?

Answer 1

make sure that there are indexes on the fields that are in the where clause when updating

Answer 2

You can periodically check for open tables:
show open tables where In_Use > 0
If the output is non-empty, then these tables are in use at that moment. While the table is locked for updating, other requests are waiting.
Added
If transactions are used, they lock the table for the duration of the update.
It is also possible that there are not enough resources on the machine.
Check mysqltuner recommendations.