Pouvez-vous l'indice des sous-requêtes?
J'ai une table et une requête qui ressemble à ci-dessous. Pour un exemple, voir ce SQL Violon.
SELECT o.property_B, SUM(o.score1), w.score
FROM o
INNER JOIN
(
SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B
) w ON w.property_B = o.property_B
WHERE o.property_A = 'specific_A'
GROUP BY property_B;
Avec mes données réelles, cette requête est de 27 secondes. Cependant, si j'ai d'abord créer w, à titre temporaire, des tables et des index property_B, l'ensemble prend environ 1 seconde.
CREATE TEMPORARY TABLE w AS
SELECT o.property_B, SUM(o.score2) AS score FROM o GROUP BY property_B;
ALTER TABLE w ADD INDEX `property_B_idx` (property_B);
SELECT o.property_B, SUM(o.score1), w.score
FROM o
INNER JOIN w ON w.property_B = o.property_B
WHERE o.property_A = 'specific_A'
GROUP BY property_B;
DROP TABLE IF EXISTS w;
Est-il un moyen de combiner le meilleur de ces deux requêtes? I. e. une seule requête avec la vitesse avantages de l'indexation de la sous-requête?
MODIFIER
Après Mehran la réponse ci-dessous, j'ai lu cette pièce de l'explication dans le Documentation de MySQL:
MySQL 5.6.3, l'optimiseur de manière plus efficace les poignées de sous-requêtes dans la clause from (qui est, les tables dérivées):
...
Pour les cas de matérialisation est nécessaire pour une sous-requête dans la clause from, l'optimiseur peut accélérer l'accès au résultat par l'ajout d'un index à l'matérialisé table. Si un tel indice permettrait ref accès à la table, il peut réduire considérablement la quantité de données qui doit être lu lors de l'exécution de la requête. Considérons la requête suivante:
SELECT * FROM t1
JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;
L'optimiseur construit un index sur la colonne de f1 à partir de derived_t2 si c'était de permettre l'utilisation de réf accès pour le plus bas coût de plan d'exécution. Après l'ajout de l'index, l'optimiseur peut traiter la matérialisés provenant de la table de la même chose que d'une table avec un index, et il bénéficie de la même manière à partir de l'index généré. Les frais généraux de la création d'index est négligeable par rapport au coût de l'exécution de la requête sans l'index. Si ref accès entraîne un coût plus élevé que certains autres de la méthode d'accès, pas d'index est créé et l'optimiseur ne perd rien.
En effet, property_B est répertorié dans la Table source. o. EXPLIQUEZ me dit qu'aucun indice est utilisé pour calculer la JOINTURE INTERNE ( .... ) w .... Je veux changer ma requête telle que l'utilisation (temporaire?) index.
Vous n'avez pas d'agrégation des fonctions (dans l'exemple affiché ci-dessus). Tout ceci est un non-sens. Aussi, il est peu probable que vous voulez un type de données FLOAT ici (en coulisse). Changer en DÉCIMAL.
...et où des expressions comme int(8) et int(11) vient-il? Le nombre entre parenthèses est (presque) pas pertinent
Merci de remarquer que j'ai manqué la SOMME(..) la fonction ci-dessus. Ils étaient présents dans le SQL de Violon. Je ne vois pas comment discuter sur le FLOTTEUR vs DÉCIMAL ou les numéros entre parenthèses sont pertinents pour cette question, pouvez-vous développer?
OriginalL'auteur physicalattraction | 2014-11-20
Vous devez vous connecter pour publier un commentaire.
Tout d'abord, vous devez savoir que la création d'une table temporaire est absolument une solution réalisable. Mais dans les cas, pas d'autre choix est applicable, ce qui n'est pas le cas ici!
Dans votre cas, vous pouvez facilement augmenter votre requête comme FrankPl signalé parce que votre sous-requête et de la main-requêtes sont des regroupement par le même champ. Si vous n'avez pas besoin des sous-requêtes. Je vais copier et coller FrankPl de la solution par souci d'exhaustivité:
Pourtant, cela ne signifie pas qu'il est impossible de venir à travers un scénario dans lequel vous le souhaitez vous pouvez l'indice d'une sous-requête. Dans quels cas vous avez deux choix, le premier est d'utiliser une table temporaire comme vous l'avez souligné vous-même, tenant les résultats de la sous-requête. Cette solution est avantageuse car elle est prise en charge par MySQL pour un long moment. C'est juste pas possible si il y a une énorme quantité de données.
La deuxième solution est d'utiliser Version de MySQL 5.6 ou au-dessus. Dans les versions récentes de MySQL de nouveaux algorithmes sont incorporés donc un index défini sur une table utilisée à l'intérieur d'une sous-requête peut également être utilisé en dehors de la sous-requête.
[Mise à JOUR]
Pour la version éditée de la question que je vous recommande la solution suivante:
Mais vous avez besoin de travailler sur la
HAVING
partie. Vous devrez peut-être modifier en fonction de votre problème.Merci de remarquer que les deux options pour lorsque vous souhaitez indice d'une sous-requête. Je vais le regarder maintenant, et de revenir à elle.
OriginalL'auteur Mehran
Il devrait être du devoir de MySQL pour optimiser votre requête, et je ne pense pas qu'il y est une façon de créer un index sur la volée. Cependant, vous pouvez essayer de forcer l'utilisation de l'indice de property_o (si vous en avez). Voir http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
Aussi, vous pouvez fusionner les de créer et de modifier des déclarations, si vous préférez.
USE INDEX (prop_B_idx)
, mais qui n'a rien fait. Ensuite, j'ai essayéFORCE INDEX (prop_B_idx)
, qui a été l'accélération de la requête à partir de 27 secondes à 2-2.5 secondes. Il est encore plus lent toutefois que la création d'une Table temporaire, l'indexation d'une colonne dans ce temp, de l'utiliser et de détruire la Table temp.OriginalL'auteur koriander
Je ne vois pas pourquoi tu aurais besoin de la rejoindre. Je suppose que
devrait donner ce que vous voulez, mais avec beaucoup plus simple et donc mieux pour optimiser l'instruction.
Dans le cas où vous avez une seule condition pour laquelle vous voulez calculer le
sum(o.score2)
, alors vous pouvez utiliser une construction commesum(if(condition, o.score2, null)) where
condition` serait votre condition where de la sous-sélection. Il y aurait encore pas besoin d'utiliser une sous-sélection.OriginalL'auteur FrankPl
Je ne suis pas vraiment familier avec MySql, j'ai surtout travaillé avec Oracle.
Si vous voulez une clause where dans la SOMME, vous pouvez utiliser décoder ou de l'affaire.
il ressemblerait à quelque chose comme ça
ou de l'affaire
OriginalL'auteur a.j. tawleed