Как ускорить Update в цикле на большой таблице?

Question

[Wade2k]

Есть таблица table1 c 3млн строк. Индекс на id есть.

Нужно в цикле на php сделать update одного поля по id последовательно для всех строк.
Написал на php скрипт, все хорошо, но каждые 1000 update отрабатывается за 8 сек. Так я буду до завтра ждать)

Explain посмотрел, индекс используется. В чем может быть проблема?
Таблица innodb

Answer 1

[Станислав Меньшов]

Может, я не так понял но зачем здесь запускать цикл , если обновляется вся таблица, где перебираются все ID последовательно.
Просто же можно

UPDATE table1
SET field = 'value'

Comments

[Wade2k]

в цикле делается update table1 set url=$newurl where id=$id;
$newurl вычисляется в функции пхп и для каждой строки он разный и зависит от других скриптов

[Andrey Tsvetkov]

и зависит от других скриптов

возможно дело в других скриптах?

не делайте апдейты в цикле, разбивайте данные по 1000 записей, и после пачкой делайте запрос на обновление, так будет значительно быстрее

[Wade2k]

Нет, не в скриптах - комментил только update - и сразу полетело.

А вот по поводу пачками - что вы имеете ввиду? Вставлять в одну транзакцию несколько update или как?

[Станислав Меньшов]

Тогда ясно ... Хотите выиграть в скорости, тогда пристегнитесь ))
Допустим есть такая структура для 10 млн строк

CREATE TABLE table1 (
  `Id` int(11) NOT NULL,
  `url` varchar(256) NOT NULL
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB

Чтобы ускорить загрузку, вам не нужно генерировать апдейт на лету.
Вам нужно выполнить отложенное вычисление в background процесс, где ваш скрипт будет
генерировать данные в файл - к примеру для SQL удобен будет CSV

# table1.csv
id,url
1,http://ya.ru
2,http://google.com
3,http://bing.com

Если у Вас PHP попробуйте создать асинхронный процесс создания файла CSV с вашими данными, например есть для этого фреймворк ReactPHP и когда процесс - success

В основном коде импортируйте данные в вашу таблицу

LOAD DATA LOCAL INFILE 'table1.csv'
    -> INTO TABLE table1
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id, url);

[Wade2k]

Да, спасибо - думаю поможет.

Answer 2

[Boris]

1. create temporary table t_tmp ...
2. insert into t_tmp values (...), (...), ...
3. update ...
4. drop temporary table t_tmp

Comments

[Wade2k]

Не понял, что имеется ввиду. Можете словами объяснить? Все закинуть в temporary и там обновлять?

[Boris]

Multiple-table syntax

Answer 3

[Denis]

я с этим столкнулся на проекте с огромными прайсами и там самое простое решение это удалять и заного порционно вставлять