Frage SQLite optimiert die Mehrfachauswahl


Ich benutze seit Jahren SQL, aber selten mehr als das einfache Einfügen und Selektieren usw. ... also bin ich kein SQL-Experte. Ich frage mich, ob ich eine Hilfe bei der Optimierung einer komplexeren SQL-Anweisung, die ich auf SQLite ausführen kann, von PHP über PDO bekommen kann.

Die Aussage scheint richtig zu funktionieren, scheint nur länger zu dauern, als ich erwartet hätte (oder vielleicht erwarte ich nur zu viel).

Dies ist das 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')
        );

Was ich habe, ist eine Liste von Abonnenten in einer oder mehreren Gruppen. Ich möchte Abonnenten zu einer Mail-Warteschlange hinzufügen, diejenigen auswählen, die zu einer oder mehreren Gruppen (1,2,3) gehören, aber diejenigen ausschließen, die auch in einer anderen Gruppe von Gruppen (4,5,6) sind.

Erstens, ist das obige SQL typisch dafür?

Zweitens, welche Indikationen sollte ich haben, um diese Arbeit so effizient wie möglich zu machen?

Derzeit dauert es ungefähr 30 Sekunden, um ungefähr 5000 Teilnehmerdatensätze (und eine Handvoll von Gruppen) auf einer durchschnittlichen LAMP zu durchlaufen.

Am Ende des Tages ist die Performance nicht so wichtig, aber ich würde diese Dinge gerne besser verstehen, so dass jeder Einblick sehr geschätzt wird.

Brad


5
2018-04-10 14:43


Ursprung


Antworten:


Die Chancen sind die zusätzlichen Joins, die dich töten. Was ist, wenn du es tust?

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')
    );

Sie möchten auch sicherstellen, dass Sie einen Index für SubscriberGroups (SubscriberID, GroupID) haben

Meine Vermutung ist, dass Subscribers bereits einen Index für ID hat, oder?

BEARBEITEN: Eine weitere Option, die schneller sein kann oder auch nicht. Schau dir die Abfragepläne von jedem an, um zu sehen ...

Dieser kann ein einzelner Index-Scan sein, der könnte schneller sein als zwei Indexsuchvorgänge, aber es hängt vom Optimierer von SQLite ab ...

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

6
2018-04-10 14:47



Eine andere Möglichkeit, die SQL zu schreiben, die schneller sein könnte (ich habe keine SQLite, auf der ich testen könnte):

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?
     )

Matts Methode sollte auch gut funktionieren. Alles hängt davon ab, wie SQLite die Abfragepläne erstellt.

Bitte beachten Sie auch meine Kommentare. Wenn diese in Ihrer Datenbank wirklich als INT-Datentypen definiert sind, wird eine zusätzliche Verarbeitung zwischen den beiden unterschiedlichen Datentypen erforderlich sein. Wenn sie Zeichenfolgen in der Datenbank sind, gibt es einen Grund dafür? Haben Sie nicht-numerische Werte in diesen Spalten?


3
2018-04-10 16:05