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.

Je suppose property_B est répertorié dans la table source o? Ce n'EXPLIQUEZ vous montrer?
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

  1. 3

    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é:

    SELECT o.property_B, SUM(o.score1), SUM(o.score2)
FROM o
GROUP BY property_B;

    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:

    SELECT o.property_B, SUM(IF(o.property_A = 'specific_A', o.score1, 0)), SUM(o.score2)
FROM o
GROUP BY property_B
HAVING SUM(IF(o.property_A = 'specific_A', o.score1, 0)) > 0;

    Mais vous avez besoin de travailler sur la HAVING partie. Vous devrez peut-être modifier en fonction de votre problème.

    Je vois maintenant que j'ai simplifié à l'extrême la question de w.r.t. le SQL de violon que j'ai donné. En effet, la requête comme mentionné dans la question, maintenant, peut être obtenu sans la rejoindre. Cependant, ce que je voulais dire c'est qu'une somme devrait être à un endroit OÙ cluase pris en compte (ce qui a été accidentellement laissé tomber lors de la prise de l'exemple plus simple) et une somme devrait être sans la clause where pris en compte. Je mettrai à jour la question.
    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

  2. 1

    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.

    Merci. J'ai essayé 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

  3. 1

    Je ne vois pas pourquoi tu aurais besoin de la rejoindre. Je suppose que

    SELECT o.property_B, SUM(o.score1), SUM(o.score2)
FROM o
GROUP BY property_B;

    devrait donner ce que vous voulez, mais avec beaucoup plus simple et donc mieux pour optimiser l'instruction.

    Je vois maintenant que j'ai simplifié à l'extrême la question de w.r.t. le SQL de violon que j'ai donné. En effet, la requête comme mentionné dans la question, maintenant, peut être obtenu sans la rejoindre. Cependant, ce que je voulais dire c'est qu'une somme devrait être à un endroit OÙ cluase pris en compte (ce qui a été accidentellement laissé tomber lors de la prise de l'exemple plus simple) et une somme devrait être sans la clause where pris en compte. Je mettrai à jour la question.
    Dans le cas où vous avez une seule condition pour laquelle vous voulez calculer le sum(o.score2), alors vous pouvez utiliser une construction comme sum(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

  4. 1

    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

    SELECT o.property_B, , SUM(decode(property_A, 'specific_A', o.score1, 0), SUM(o.score2)
FROM o
GROUP BY property_B;

    ou de l'affaire

    SELECT o.property_B, , SUM(CASE
                            WHEN property_A = 'specific_A' THEN o.score1 
                            ELSE 0 
                            END ), 
SUM(o.score2)
FROM o
GROUP BY property_B;

    OriginalL'auteur a.j. tawleed