простая база с одним полем, но много записей. как быстро организовать проверку и добавление?

[alexey980]

хорошо.
если, скажем, для данного конкретного примера оставить все-таки таблицу с одним полем id.
уже без всяких индексов.
перед добавлением новых id: aaa, bbb, ccc... делать предварительную выборку из таблицы вроде
SELECT * FROM users WHERE id in ('aaa', 'bbb', 'ccc')
потом сравнивая эту выборку с входящим списком новых id, удалить из него дубликаты.
(здесь уже можно и простым сравнением строк или регулярными выражениями обойтись)
и затем уже этот усеченный список вставлять в таблицу с помощью INSERT, но уже без всяких игноров.
так будет быстрее?

 

[Тугай]

Сравнивать что-то на php - будет медленней. Для 2к записей можно попробовать через временную таблицу. Скорей всего отличия будут незначительные.
Все что остается это смотреть на настройку самой бд. Там нужен шаман и бубен

innodb_buffer_pool_size - должен быть достаточный, чтоб индекс в памяти оставался, тогда медленным будет только первое добавление.

 

[alexey980]

вопрос сейчас только в одном. сразу вставлять в таблицу 2К записей через INSERT IGNORE... или же сначала через SELECT... WHERE... IN... сделать выборку дубликатов, усечь из 2К записей дубликаты и вставлять уже усеченный список через обычный INSERT.
индекс ведь тормозит таблицу?
в первом варианте нужно использовать индексную таблицу, а во втором можно без индексов.

 

[Тугай]

alexey980
Без индекса SELECT по миллионам записей будет так же тормозить как и текстовый файл. Поиск будет последовательный, а не по индексу.
Индекс ускоряет SELECT, но немного замедляет INSERT.

SELECT... WHERE... IN нужно сделать N проверок при выборе и потом M<=N при вставке, а с INSERT IGNORE только N проверок.
Можно сделать индекс не уникальным и тогда SELECT... WHERE... IN и потом вставка усеченного списка уже не требует M, но это просто свой контроль уникальности, который не будет быстрее, чем готовый в mysql.

Хотя попробуйте с SELECT... WHERE... IN можно обойтись одним полем в таблице и не использовать поле с AUTO_INCREMENT ну и можно поиграться еще с удалением и пересозданием индекса.
Индексация может занимать пару секунд всего.

 

[С.]

или же сначала через SELECT... WHERE... IN... сделать выборку дубликатов, усечь из 2К записей дубликаты и вставлять уже усеченный список через обычный INSERT.

SELECT... WHERE... IN... + обычный INSERT = INSERT IGNORE

 

[WMix]

а что происходит чаще на сегодняшний день чтение или запись, нужно ли записать как можно быстрее или можно подождать если сервер занят (queue, INSERT DELAYED, промежуточная табличка или даже твое старое решение CSV а к вечеру все залить в табличку)... решений можен быть множество в зависимости от постановки задачи! самое важное понять что ресурсы не безграничны и ускорив одно замедляешь другое.

 

[alexey980]

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

 

[WMix]

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

 

[alexey980]

пришёл пакет с новыми id.
проверяем, нет ли повторов с теми, что уже есть в базе.
добавляем отфильтрованный пакет в табличку и передаем его далее на обработку (в другой сценарий).

следующий пакет может прийти в любое время.

 

[WMix]

на все года проверка повторов?

 

[alexey980]

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

 

[WMix]

тогда делай 2ю таблицу, заливай туда данные, и остальные операции делай непосредственно в базе,
1. удаление дубликатов, (остались только новые), можно их отправлять далее,
2. insert into select в архив

 

[fixxxer]

у меня большие сомнения, что это даст какие-то преимущества перед load data infile ignore

 

[WMix]

таблица не заблокированна на момент записи, не нужно дополнительное поле id которое придуманно чтоб использовать простую команду, деление на подзадачи, заливка, выборка ответа, архивация данных, а вообщем теже яица только пасхальные

 

[alexey980]

а что если входящий пакет сохранять в файл, а потом через load data infile ignore добавлять уже к существующей таблице. load же, наверное, будет быстрее, чем insert values (...),(...),(...)...
интересно, можно ли таким образом сократить время на пакетную вставку.
а там уже по дополнительному полю auto_increment делать выборку последних добавленных (или по timestamp), предварительно запомнив соответствующее значение до вставки.
завтра попробую.

сегодня, кстати, столкнулся с еще одной проблемой.
auto_increment ведет себя, мягко говоря, странно.
иногда, даже в случае если срабатывает ignore и вставка не происходит, auto_increment все равно увеличивается +1, и следующая вставка продолжается не с позиции n+1, а с позиции n+2.
это, конечно, не проблема для выборки последних добавленных по n > n_max_до_вставки,
но если учитывать, что я использовал n для быстрого подсчета общего числа записей в таблице, то select max(n) уже не равно select count(n)
а было бы удобнее использовать именно первый запрос, так как времени на его обработку требуется гораздо меньше.

 

[fixxxer]

alexey980
http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html

 

[fixxxer]

auto_increment ведет себя, мягко говоря, странно.

так задумано, сначала автоинкремент а потом операция вставки (не важно, с ошибкой она проходит или нет)
иначе невозможно гарантировать уникальность автоинкрементного значения при параллельных транзакциях

если не нравится не используй auto_increment. auto_increment дает просто уникальный id, не надо в него пытаться вкладывать какой то смысл

 

[alexey980]

танцы с бубном продолжаются. пришел пока к такому решению.
в таблице users (тип InooDB) 2 поля:
id - varchar(22), primary key;
time - timestamp, по умолчанию CURRENT_TIMESTAMP (дополнительное вспомогательное поле).

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

// сохраняем входящий пакет с новыми неотфильтрованными id во временный файл
file_put_contents('new_id_not_filtered.txt',$_POST["new_id"]);

// устанавливаем соединение с БД
$db = mysql_connect("localhost","...","...");
mysql_select_db("..." ,$db);

//определяем количество записей в таблице
$sql = "SELECT COUNT(*) FROM users";
$result = mysql_query($sql);  
$row = mysql_fetch_array($result);	
echo "количество записей в таблице: ".$row[0];

// запоминаем текущее время до вставки в формате mysql timestamp
$before_insert_time = "'".date("Y-m-d H:i:s")."'";

// вставка новых id из временного файла, дубликаты отсеиваются
$sql = "LOAD DATA LOCAL INFILE 'new_id_not_filtered.txt' IGNORE INTO TABLE users(id)";
$result = mysql_query($sql);

// возвращаем id которые были добавлены в результате последней вставки
$sql = "SELECT id FROM users WHERE time >= $before_insert_time";
$result = mysql_query($sql);
$n_new = 0;
while ($row = mysql_fetch_array($result)){
	$n_new++;
	//.... передаем дальше в другой сценарий последние добавленные id: $row[0] .... ;
}
echo "добавлено новых id: $n_new";		

//закрываем соединение с БД
mysql_close($db);

при этом, заметил одну странную вещь. хотя пока еще на 100% не уверен.
но похоже, если убрать из сценария запрос SELECT COUNT(*) FROM users, то последующая вставка LOAD DATA LOCAL INFILE IGNORE будет выполнятся гораздо медленнее.
другими словами, запрос SELECT COUNT(*) FROM users перед загрузкой данных в таблицу каким-то образом ускоряет последующую загрузку LOAD DATA LOCAL INFILE IGNORE.
причем довольно ощутимо. возможно это как-то связано с выстраиванием индекса. есть ли этому логическое объяснение?

примечание:
тестирование проводилось на таблице с 2-мя млн. id. при этом в таблицу добавлялся пакет 2К id, в котором были вперемешку 50% на 50% как новые уникальные id, так и те, что уже есть в таблице.