Comment améliorer la vitesse de regroupement

Question

J'ai une grande table avec plus de 3 milliards de lignes et l'exécution de ma requête prend désormais 3 jours.

Voici ma requête :

insérer dans les mots de fréquence (mot, fréquence, nom de fichier)
sélectionnez le mot, comptez (*) comme fréquence, max (nom de fichier)
de tous les motstemp
regrouper par mot

Fondamentalement, ma requête consiste à regrouper par mot à partir de la table allwordstemp, et je veux connaître le filename lorsque la fréquence = 1, c'est pourquoi j'ai ajouté max(filename), car le nom de fichier doit être inclus dans la fonction d'agrégation, comme max. Si fréquence > 1, je n'ai pas non plus besoin de la valeur du nom de fichier. Les deux tables ont 2 index sur le mot et le nom de fichier.

table allwordstemp (le nom de fichier est un fichier d'identification) :

CREATE TABLE `allwordstemp` (
  `mot` varchar(45) NULL PAR DÉFAUT,
  `nom de fichier` int(11) NULL PAR DÉFAUT,
  CLÉ `idx_allwordstemp_word` (`mot`),
  CLÉ `idx_allwordstemp_filename` (`filename`)
) MOTEUR=InnoDB DEFAULT CHARSET=utf8;

tableau de mots de fréquence :

CREATE TABLE `fréquencemots` (
  `mot` varchar(45) NULL PAR DÉFAUT,
  `fréquence` int(11) NULL PAR DÉFAUT,
  `nom de fichier` int(11) NULL PAR DÉFAUT,
  CLÉ `idx_ Frequencywords_word` (`mot`),
  CLÉ `idx_fréquencewords_fréquence` (`fréquence`),
  CLÉ `idx_ Frequencywords_filename` (`filename`)
) MOTEUR=InnoDB DEFAULT CHARSET=utf8;

Expliquer la sélection :

+----+-------------+------------- -+----------------+-------+---------------+------- ---- ------------+---------+------+------------+--- ---- ---+-------------+
| id | select_type table | partitions | clé_possible | lignes |
+---------+--------+--------------+--------------- -+-- -----+---------------+----------------+-- -- -----+------+------------+--------------+--------------- -- +
| 1 | SIMPLE | allwordstemp | NULL | idx_allwordstemp_word |
+---------+--------+--------------+--------------- -+-- -----+---------------+----------------+-- -- -----+------+------------+--------------+--------------- -- +
Comment puis-je accélérer ma requête ? 

Answer 1

Votre filtre semble être basé sur la fréquence, et non sur les mots ou les noms de fichiers. Je commencerais donc par indexer tous les mots par fréquence.

Ensuite, en supposant que la fréquence soit un nombre entier, j'ajouterais une clause WHERE comme celle-ci :

select word, max(filename)
from allwordstemp
where frequency = 1
group by word

Ce qui précède vous donnera une liste de tous les mots qui n'apparaissent qu'une seule fois dans différents noms de fichiers.

J'espère que cela vous aidera, et bonne chance !