Словарь встречающихся слов в базе MySQL

Question

Дано: таблица данных MySQL ("Данные"), одно поле в которой содержит набор слов через запятую. Нужно: составить, что-то типа индекса базы, то есть сделать список всех слов, которые встречаются.

Что я сейчас делаю, создал таблицу "Словарь" (которая содержит ключевое слово и массив документов к которых оно встречается (JSON)), последовательно по одной записи из таблицы "Данные" беру из каждой записи набор слов, каждое слово проверяю в таблице "Словарь", если совпадений нет, то создаю новую запись, если совпадение есть, то обновляю существующую запись, добавляя к ней ID документа из таблицы "Данные"

Оно то все, конечно, работает, НО, в таблице "Данные" 600к записей, и за 12 часов мой скрипт перебрал 100к записей. Сначала, работал резво 20-30 записей в секунду проверял, но с ростом базы "Словарь" быстродействие заметно упало, до 1-2 записей в секунду, подозреваю что чем дальше тем медленее будет,

Может есть какие-то другие методы создать словарь слов встречающихся в записях MySQL, более производительный? Необязательно использовать PHP, буду рад любому решению с хорошим быстродействием.

Код (вызываю последовательно скрипт со страницы AJAXом - потому что, во-первых, не знаю, как заставить работать скрипт "вечно", во-вторых, для визуализации процесса):

var indexID = 0;
function indexerFunc (id) {
        $.post( "indexer.php", { index: id })
          .done(function( data ) {
               if (indexID <= 680160) {
                   
                   $("#count").html (indexID);
                   indexID = indexID + 1;

                   indexerFunc (indexID);
               }else{
                   console.log(data);
               }
          });
    }

в PHP получаю запись из "Данные" по ID

$id = $_POST["index"];
$resource = $connDataBase->query('SELECT *  FROM `basesearch` WHERE `id` ='.$id);
$row = $resource->fetch_assoc();

Разбиваю по запятой на массив, каждое слово проверяю в таблице "Словарь" и создаю/обновляю запись

foreach (explode(',', $row["search"]) as $word) {
$resourceSearch = $connDataBase->query("SELECT *  FROM `dictonary` WHERE `word` LIKE '" . $word . "'");
                $rowCount = 0;
                $rowSS = [];
                if ($resourceSearch) {
                    $rowSS = $resourceSearch->fetch_assoc();
                }

                $rowCount = count($rowSS);

                if ($rowCount == 0) {
                    // создаем новый
                    $firstSymb = mb_substr($word, 0, 1);
                    $masDoc = [];
                    array_push($masDoc, intval($id));
                    $insertMas = json_encode($masDoc);
                    $connDataBase->query("INSERT INTO `dictonary` (`id`, `letter`, `word`, `docs`, `count`) VALUES (NULL, '" . $firstSymb . "', '" . $word . "', '" . $insertMas . "', '1')");
                } else {
                    //обновляем существующий
                    $masDoc = json_decode($rowSS["docs"]);
                    array_push($masDoc, intval($id));
                    $insertMas = json_encode($masDoc);
                    $co = intval($rowSS["count"]) + 1;
                    $idd = $rowSS["id"];
                    $connDataBase->query("UPDATE `dictonary` SET `docs` = '" . $insertMas . "', `count` = '" . $co . "' WHERE `dictonary`.`id` = " . $idd);
                }
}

Заранее благодарю за помощь.

Answer 1

Таблица с исходными данными:

CREATE TABLE phrases (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, csv TEXT);

Создаём таблицу под слова/теги

CREATE TABLE tags (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, tag VARCHAR(16) UNIQUE);

Заполняем её (показанный текст запроса - не более 4*4=16 тегов)

INSERT IGNORE INTO tags (tag)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(phrases.csv, ',', numbers1.num * 4 + numbers2.num + 1), ',', -1)
FROM phrases
CROSS JOIN ( SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) numbers1
CROSS JOIN ( SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ) numbers2

Создаём связующую таблицу

CREATE TABLE phrases_to_tag (p_id BIGINT UNSIGNED NOT NULL, 
                             FOREIGN KEY (p_id) REFERENCES phrases(id),
                             t_id BIGINT UNSIGNED NOT NULL, 
                             FOREIGN KEY (t_id) REFERENCES tags(id) );

Заполняем её

INSERT INTO phrases_to_tag (p_id, t_id)
SELECT phrases.id, tags.id
FROM phrases
JOIN tags ON FIND_IN_SET(tags.tag, phrases.csv);

fiddle

Получить из этой структуры именно JSON - элементарно. Вопрос только, нужно ли...

БЛОГ НА HUSL

Словарь встречающихся слов в базе MySQL

Ответы (1 шт):