Optimisation de l'insertion multi-sélection dans SQLite

Question

J'utilise SQL depuis des années, mais rarement plus que de simples insertions et sélections, etc... Je ne suis donc pas un expert en SQL. Je me demandais si je pouvais obtenir de l'aide pour optimiser une instruction SQL plus complexe que j'exécute sur SQLite, à partir de PHP via PDO.

La déclaration semble fonctionner correctement, mais prend plus de temps que prévu (ou peut-être ai-je trop d'attentes).

Il s'agit du SQL :

INSERT OR IGNORE INTO MailQueue(SubscriberID, TemplateID)
    SELECT Subscribers.ID, '1' AS TemplateID
    FROM Subscribers 
    INNER JOIN SubscriberGroups ON Subscribers.ID=SubscriberGroups.SubscriberID
    WHERE SubscriberGroups.GroupID IN ('1', '2', '3')
    AND Subscribers.ID NOT IN 
        ( 
        SELECT Subscribers.ID FROM Subscribers 
        INNER JOIN SubscriberGroups ON Subscribers.ID=SubscriberGroups.SubscriberID
        WHERE SubscriberGroups.GroupID IN ('4', '5', '6')
        );

Ce que j'ai, c'est une liste d'abonnés, dans un ou plusieurs groupes. Je veux ajouter des abonnés à une file d'attente, en sélectionnant ceux qui appartiennent à un ou plusieurs groupes (1,2,3), mais en excluant ceux qui font également partie d'un autre ensemble de groupes (4,5,6).

Tout d'abord, le code SQL ci-dessus est-il représentatif de la manière de procéder ?

Deuxièmement, quels sont les indicateurs dont je dois disposer pour que ce travail soit le plus efficace possible ?

Actuellement, il faut environ 30 secondes pour parcourir environ 5000 enregistrements d'abonnés (et une poignée de groupes) sur un LAMP de taille moyenne.

En fin de compte, la performance n'est pas si critique, mais j'aimerais mieux comprendre ce genre de choses.

Brad

Answer 1

Il y a de fortes chances que les joints supplémentaires vous tuent. Que faire si vous le faites ?

SELECT Subscribers.ID, '1' AS TemplateID
FROM Subscribers 
WHERE EXISTS( SELECT *
                FROM SubscriberGroups
               WHERE Subscribers.ID=SubscriberGroups.SubscriberID
                                 AND SubscriberGroups.GroupID IN ('1', '2', '3') )

  AND NOT EXISTS( SELECT *
                    FROM SubscriberGroups
                   WHERE Subscribers.ID=SubscriberGroups.SubscriberID 
                     AND SubscriberGroups.GroupID IN ('4', '5', '6')
    );

Vous devez également vous assurer que vous disposez d'un index sur SubscriberGroups( SubscriberID, GroupID ).

Je suppose que les abonnés disposent déjà d'un index sur l'ID, n'est-ce pas ?

EDIT : Une autre option, qui peut ou non être plus rapide. Regardez les plans de requête de chacun pour voir...

Il peut s'agir d'un balayage d'un seul index qui pourrait est plus rapide que deux recherches d'index, mais cela dépend de l'optimiseur de SQLite...

SELECT Subscribers.ID, '1' AS TemplateID
FROM Subscribers 
INNER JOIN( SELECT SUM( CASE WHEN GroupID IN('1', '2', '3') THEN 1 ELSE 0 END ) AS inGroup,
                   SUM( CASE WHEN GroupID IN('4', '5', '6') THEN 1 ELSE 0 END ) AS outGroup,
                   SubscriberID
                            FROM SubscriberGroups
                         WHERE SubscriberGroups.GroupID IN ('1', '2', '3', '4', '5', '6' )
          ) SubscriberGroups
       ON Subscribers.ID=SubscriberGroups.SubscriberID
      AND inGroup  > 0
      AND outGroup = 0

Answer 2

Une autre façon d'écrire le SQL qui pourrait être plus rapide (je n'ai pas SQLite sur lequel tester) :

SELECT
     S.ID,
     '1' AS TemplateID     -- Is this really a string? Does it need to be?
FROM
     Subscribers S
LEFT OUTER JOIN SubscriberGroups SG ON
     SG.SubscriberID = S.ID
WHERE
     SG.SubscriberID IS NULL AND
     EXISTS
     (
          SELECT
               *
          FROM
               SubscriberGroups SG2
          WHERE
               SG2.SubscriberID = S.ID AND
               SG2.GroupID IN ('1', '2', '3')  -- Again, really strings?
     )

La méthode de Matt devrait également fonctionner. Tout dépend de la façon dont SQLite décide de créer les plans de requête.

Veuillez également prendre note de mes commentaires. Si ces types de données sont réellement définis comme des types INT dans votre base de données, il y aura un traitement supplémentaire pour convertir les deux types de données différents. S'il s'agit de chaînes de caractères dans la base de données, y a-t-il une raison à cela ? Avez-vous des valeurs non numériques dans ces colonnes ?