Таблица 500к записей (InnoDB), простой запрос - 5 минут

[workOnFood]

Всем доброго дня.

Есть таблица -

CREATE TABLE IF NOT EXISTS `div_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
`price` float NOT NULL,
`currency` varchar(20) NOT NULL,
`category` text NOT NULL,
`picture` varchar(255) NOT NULL,
`delivery` tinyint(1) NOT NULL,
`ordering` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`vendor` varchar(255) NOT NULL,
`description` text NOT NULL,
`barcode` varchar(255) NOT NULL,
`available` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=19097084 ;

Количество записей - 562116

В my.ini -

innodb_buffer_pool_size=500M
innodb_additional_mem_pool_size=50M
innodb_file_io_threads=8
innodb_lock_wait_timeout=50
innodb_log_file_size = 100M
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=0
tmp_table_size = 32M

Сам my.ini в аттаче.

Делаю запрос - 'select id from `div_book` limit 500000, 100'
Время выполнения - 381.21597099304

Первый раз работаю с InnoDB и таким объемом данных. Помогите разобраться.

 

[Фанат]

Всё правильно, лимит так и работает.
для чего тебе понадобился такой?

 

[workOnFood]

Всё правильно, лимит так и работает.
для чего тебе понадобился такой?

Мне нужно сравнить айдишники из таблицы со значениями из xml. Если пытаюсь выгрузить все айдишники мускул падает. Вот я и хотел выгружать по 100 и сверять, но это затянулось на сутки, и все еще работало. Тогда я вырубил скрипт и дописал небольшой лог, чтобы видеть что происходит... ну и увидел что запросы идут все медленнее и медленнее.

 

[Фанат]

а зачем айдишники ВЫГРУЖАТЬ для сравнения?!

 

[workOnFood]

Причем сравнить нужно именно в таком порядке те что из базы с теми что из xml, чтобы если в xml его нет удалить его из базы. Айдишники идут не по порядку т.е. перебрать все по очереди не получится, хотя наверное и в этом случае было бы очень долго. В голову не приходит ни одного более менее нормального решения. Полностью перезаливать базу каждый раз не охота - долго, и как-то тупо.

 

[Фанат]

не понял. в XML тоже порядка пол-ляма записей?

 

[workOnFood]

не понял. в XML тоже порядка пол-ляма записей?

Ага

UPD

Я его разбиваю на куски, потом загружаю по очереди и сверяю с базой на предмет изменений, попутно пишу айдишники в массив чтобы потом сверить с айди из базы.

 

[workOnFood]

Смотрю в сторону var_export(). Писать айдишники в текстовый файл, и обновлять его каждый раз при обновлении таблицы из xml. Пока единственное что приходит на ум... Хотя это кажется костылем все-таки, может как-то можно более менее быстро получить их средствами мускула?

 

[Фанат]

в перезаливать ты пробовал? сколько времени занимает?

 

[workOnFood]

в перезаливать ты пробовал? сколько времени занимает?

Около 14 часов.

UPD И это одна таблица, а всего их семь. Они правда отличаются по размеру, но в сумме думаю выйдет дня на три четыре, а хотелось бы чтобы хотябы за ночь обновлялось, т.к. обновлять надо раз в неделю минимум.

 

[Фанат]

что-то многовато для заливки.
делай из xml дамп.
и освой отключение индексов при заливке.

 

[workOnFood]

что-то многовато для заливки.
делай из xml дамп.
и освой отключение индексов при заливке.

Да, идея с дампом мне как-то в голову не приходила... И про отключение индексов тоже) Но я изначально не планировал так делать, ведь обновление - т.е. сравнение каждой строки и перезапись в случае несовпадения происходит в разы быстрее. Единственное узкое место, это удаление отсутствующих в xml записей.

 

[Redjik]

Дак сделай таблицу с полями как в xml и дампом залей... потом уже по ней сравнивай - под конец просто удаляй все записи.

 

[Фанат]

Redjik
ты ваще не понял задачу. ему не надо сравнивать поля, а только айдишники.
поэтому твоё решение можно сократить до "дампом залей". всё - больше ничего делать не надо, как я уже написал

 

[workOnFood]

Дак сделай таблицу с полями как в xml и дампом залей... потом уже по ней сравнивай - под конец просто удаляй все записи.

Нифига не понял) Я и так сравниваю записи... и обновляю если не одинаковые. А что удалять в конце? Ты имеешь ввиду удалять все записи таблицы и заливать новый дамп? Зачем тогда что-то сравнивать?

 

[Redjik]

Фанат
как раз понял - у меня подобная и была задача... только я не удалял запись, а делал published = 0, потому что товар может появиться в следующих выгрузках

workOnFood
mysql одним запросом за вполне приемлемое время проверит записи, тут php даже не надо задействовать

 

[workOnFood]

workOnFood
mysql одним запросом за вполне приемлемое время проверит записи, тут php даже не надо задействовать

Так а откуда мускул будет брать айдишники с которыми сверять надо? Все в запрос запихать?

 

[Redjik]

У меня вообще забавная ситуация - заказчики - не очень умные люди...
Сначала заказали магазин за 300к, а потом отказались от VDS - он сейчас крутится НА ДЕШМАНСКОМ ХОСТИНГЕ
Поэтому были свои ограничения на выгрузку.
Примерно 100к товаров. И ограничения на время скрипта в 30 секунд.
Первый этап - распаршиваю CSV в базу данных.
Второй этап - пользователь выбирает и устанавливает наценки на категории товаров (или подтверждает прошлые).
Третий этап - один запрос в бд, который

1) создает запись + добавляет в крон для закачивания картинок (предоставлены партнером)
2) обновляет записи
3) если запись отсутствует - ставит published 0

ЗЫ. очень сильно нагнал - пардон ... 3 запроса у меня идет
1) если есть id в товарах и нет id для парсинга - published 0
2) если есть id для парсинга, но нет в товарах - добавляю
3) если есть id и там и там - update

 

[Redjik]

workOnFood
блин у тебя одна таблица - текущие записи, вторая таблица - записи из xml... у тебя же есть id в xml
не вижу проблемы - единственное - запрос я пол дня писал этот, чтобы он быстро обрабатывался =)

 

[Фанат]

Redjik
подобная задача у всех была. не надо думать, что ты один такой уникальный.

здесь задача куда проще. она заканчивается на этапе " распаршиваю CSV в базу данных".
если бы в XML айдишники не совпадали с теми, что в базе (как это обычно бывает в подобных задачах) - тогда да, нужно было бы что-то выдумывать