Ich habe eine Tabelle mit ca. 5 Millionen Datenzeilen (Artikeln). Ich habe die folgende Abfrage für die Volltextsuche im Titel der Artikel in zwei verschiedenen Sprachen. Das Problem dabei ist, dass die Ausführung etwa 15 Sekunden dauert.MySQL version: 5.6.29-log

Hier ist die Abfrage:

SELECT `id`, `title`, `title_fa` FROM
    (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`unique` AS `unique`, `p`.`date` AS `date` FROM `articles` `p` LEFT JOIN `authors` `a` ON  `p`.`unique` =  `a`.`unique` WHERE 1 AND MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE)
    UNION
    SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`unique` AS `unique`, `p`.`date` AS `date` FROM `articles` `p` LEFT JOIN `authors` `a` ON  `p`.`unique` =  `a`.`unique` WHERE 1 AND MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE)) AS `subQuery`
GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10;

Dies ist die Tabellenstruktur:

CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL,
  `title` text COLLATE utf8_persian_ci NOT NULL,
  `title_fa` text COLLATE utf8_persian_ci NOT NULL,
  `description` text COLLATE utf8_persian_ci NOT NULL,
  `description_fa` text COLLATE utf8_persian_ci NOT NULL,
  `date` date NOT NULL,
  `unique` tinytext COLLATE utf8_persian_ci NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;

ALTER TABLE `articles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `unique` (`unique`(128)),
  ADD FULLTEXT KEY `TtlDesc` (`title`,`description`);
  ADD FULLTEXT KEY `Title` (`title`);
  ADD FULLTEXT KEY `faTtlDesc` (`title_fa`,`description_fa`);
  ADD FULLTEXT KEY `faTitle` (`title_fa`);
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

Erster Verbesserungsschritt:

Bei der Suche ist SO bin ich auf diesen Beitrag gestoßen:

Kombinieren von UNION- und LIMIT-Operationen in MySQL-Abfragen

Mit der vorgeschlagenen Methode habe ich meine Abfrage wie folgt geändert:

SELECT `id`, `title`, `title_fa` FROM
    (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON  `p`.`unique` =  `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) LIMIT 0,100
    UNION
    SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON  `p`.`unique` =  `a`.`unique` WHERE MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE) LIMIT 0,100) AS `subQuery`
GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10

Die Leistung war erstaunlich und die Ausführung der Abfrage dauerte ungefähr 0,04 Sekunden. Das Problem mit der Sortierung war, dass ich neuere Artikel gerne zuerst aufgelistet habe, aber diese Abfrage ist dazu nicht in der Lage. Ich bin mir auch nicht sicher, wie die nächsten Ergebnisse (d. H. Die nächsten 10 Ergebnisse - zweite Seite der Ergebnisse) abgerufen und angezeigt werden können.

Zweiter Verbesserungsschritt:

uf der Suche nach weiteren Informationen zu SO bin ich auf Folgendes gestoßen:

SQL Query - Verwenden von Order By in UNION

Und meine Anfrage sah wie folgt aus:

SELECT `id`, `title`, `title_fa`, `unique`, `date` FROM
    (SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON  `p`.`unique` =  `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE)  ORDER BY `p`.`date` DESC LIMIT 0,20) AS `subQueryE`
    UNION ALL
SELECT `id`, `title`, `title_fa`, `unique`, `date` FROM
    (SELECT `f`.`id` AS `id`, `f`.`title` AS `title`, `f`.`title_fa` AS `title_fa`, `f`.`date` AS `date`, `f`.`unique` AS `unique` FROM `articles` `f` LEFT JOIN `authors` `a` ON  `f`.`unique` =  `a`.`unique` WHERE MATCH (`f`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE)  ORDER BY `f`.`date` DESC LIMIT 0,20) AS `subQueryF`
GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10

Die Leistung war besser, aber nicht zufriedenstellend, da es ungefähr 7 Sekunden dauerte. Es brachte ein weiteres Problem auf, das sogar mitGROUP BY unique doppelte Zeilen waren noch in den Ergebnissen vorhanden.

Dritter Schritt

Ich habe einen weiteren Test durchgeführt, indem ich die folgende Abfrage ausgeführt habe, um bessere Ergebnisse zu erzielen:

SELECT `p`.`id` AS `id`, `p`.`title` AS `title`, `p`.`title_fa` AS `title_fa`, `p`.`date` AS `date`, `p`.`unique` AS `unique` FROM `articles` `p` LEFT JOIN `authors` `a` ON `p`.`unique` = `a`.`unique` WHERE MATCH (`p`.`title`) AGAINST ('"heat"' IN BOOLEAN MODE) OR MATCH (`p`.`title_fa`) AGAINST ('"گرما"' IN BOOLEAN MODE) GROUP BY `unique` ORDER BY `date` DESC LIMIT 0,10

Aber die Ausführungszeit war schrecklich und erreichte mehr als 100 Sekunden.

Jede Hilfe ist mehr als willkommen und vielen Dank im Voraus.