РЕально быстрая выборка Select из базы данных, как?

[silmarion]

Доброго дня, в общем уже несколько дней мучаюсь вопросом.

Есть огромная база(200кк)
вида
id;author;book
id;author;book
id;author;book

мне присылают файлы вида list.txt
издательство:автор
издательство:автор
издательство:автор

Нужно брать из файла колонку 'книга'

делать поиск по базе по автору

и сохранять если найдено совпадение по колонке автор в файл found.txt
издательство:книга
издательство:книга
издательство:книга

а если не найдено, то сохранять в файл not_found.txt
издательство:автор
издательство:автор
издательство:автор

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

Вот пример скрипта

<?php
set_time_limit(0);
//ini_set('display_errors',1);error_reporting(E_ALL);   
$dbhost = "127.0.0.1";$dbuser = "root";$dbpassword = "";$dbname = "i";$link = mysql_connect($dbhost, $dbuser, $dbpassword);mysql_select_db($dbname, $link);
/////////////////////////////////////////////////////
$sqlCount = 0;$sqlTime = 0;$timeStart = microtime( TRUE);
/////////////////////////////////////////////////////
$data = file("find.txt");
$ff = fopen("found.txt", "a");
$nf = fopen("found_not.txt", "a");
for ($i = 0; $i < count($data); $i++) {
$pieces = explode(":", $data[$i]);
$pie = preg_replace("/\s/","",$pieces[1]);

$ath = mysql_query("select `author` from `izd` where `book` = '$pie'  LIMIT 1");
$sqlCount++;
if($ath) {
$author = mysql_fetch_array($ath);

if(!empty($author['author'])){
$mytext1 = $pieces[0].":".$author['author']."\n";
$test = fwrite($ff, $mytext1); }

if(empty($author['author'])){
$mytext2 = $pieces[0].":".$pieces[1];
$test = fwrite($nf, $mytext2); }

}
else { echo "<p><b>Error: ".mysql_error()."</b></p>"; exit(); }
}
//////////////////////////////////////////////////////////////
$sqlTime += microtime( TRUE) - $timeStart;
printf( '%d запросов за %01.2f секунд.', $sqlCount, $sqlTime);
/////////////////////////////////////////////////////////////
?>

Как всё это дело ускорить?
Потому что ну оооооочень долго выборка идет.

Пишут на форумах что можно подобное запускать в много потоков и т.п.
Но как реализовать не нашел.

 

[SocMaster]

Ну как вариант, самый простой. если это единоразово сделать надо. то создайте например 5 файлов, в каждом поставьте лимит в выборке, например в первом файле, первых 500 000 в втором с 500 000 записи по миллионную и так далее

 

[CAPAXA]

Попробуйте:
1) Использовать индексы в БД
2) Делать запрос в БД, не по каждой строчке отдельно, а "пакетно" (например по 100) используя IN

Или как вариант выгружать из БД всю инфу, перегонять в ассоциативный массив, и по ключам делать проверку на наличие.

 

[silmarion]

Попробуйте:
1) Использовать индексы в БД
2) Делать запрос в БД, не по каждой строчке отдельно, а "пакетно" (например по 100) используя IN

Или как вариант выгружать из БД всю инфу, перегонять в ассоциативный массив, и по ключам делать проверку на наличие.

индексы проставлены

Насчет ассоциативного массива идея интересная, но там ведь нельзя делать проверку на уникальность колонок?

А не мог бы сделать набросок с IN?

$query="select `author` from `izd` where `book` in (";
for($j = 0; $j < count($urls); $j++) {
$pieces = explode(":", $urls[$j]);
$pie = preg_replace("/\s/","",$pieces[1]);
$query.="'$pie', ";
}
$query.=")";
$query=str_replace("', )","')",$query);
//echo $query;

$ath = mysql_query($query);
$sqlCount++;
while($author = mysql_fetch_array($ath)){
    printf ("%s:%s<br>", $pieces[0], $author[0]);
}

Намного быстрее чем по одиночке, но опять проблема возникла, как и с multi_query пробовал, как если найдено совпадение подставить если найдена book в бд как его совместить с издательством?
И если большой текстовый файл делать то получаю MySQL server has gone away
Надо еще на части разбить запросы

Ну как вариант, самый простой. если это единоразово сделать надо. то создайте например 5 файлов, в каждом поставьте лимит в выборке, например в первом файле, первых 500 000 в втором с 500 000 записи по миллионную и так далее

Все равно медленно.

 

[CAPAXA]

Забыл особенность IN, он не работает с индексами. Поэтому получится медленнее чем было.

Насчет ассоциативного массива идея интересная, но там ведь нельзя делать проверку на уникальность колонок?

Что значит уникальность колонок?

 

[Hannibal86]

Что значит уникальность колонок?

Отсутствие повторяющихся записей

 

[CAPAXA]

Так это пусть БД контролирует.

 

[Hannibal86]

100%. Иначе лишняя ресурсозатрата.

 

[silmarion]

Забыл особенность IN, он не работает с индексами. Поэтому получится медленнее чем было.

Что значит уникальность колонок?

а как насчет multi_query?

$urls = file("ttt.txt");
$query  = "SELECT CURRENT_USER();";
for ($i = 0; $i < count($urls); $i++) {
$pieces = explode(":", $urls[$i]);
$pie = preg_replace("/\s/","",$pieces[1]);
$query.= "select `author` from `izd` where `book` = '$pie';";
}
if (mysqli_multi_query($link, $query)) {
  do {
  
  if ($result = mysqli_store_result($link)) {
  while ($row = mysqli_fetch_row($result)) {
    
  printf("%s\n", $row[0]);
  $sqlCount++;
  }
  mysqli_free_result($result);
  }
  
  if (mysqli_more_results($link)) {
  //  printf("-----------------\n");
  }
  } while (mysqli_next_result($link));
}


mysqli_close($link);

Тоже вроде побыстрее, но не могу вкурить как мне выводить и сохранять $pieces[0].":".$row[0]
и если не найдено сохранять $pieces[0].":".$pieces[1]
?
никак не могу с порядком for разобраться((
найденные $row[0] выводит
а если подставляешь $pieces[0] то естественно выводит первое значение во всех строках

 

[Шумадан]

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

допустим есть табличка
books
id;author;book

вы на основе файла создаёте другую таблицу
search_book
id;publisher,author

на оба поля автора (author) в обоих таблицах ставите индекс

потом
SELECT sb.id, sb.publisher, sb.author, b.author as found_author FROM search_book sb
LEFT JOIN books b ON b.author = sb.author

в результате получите табличку
id, publisher, author, found_author

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