SQLite optimizing multi-select insert

Question

I've been using SQL for years now but rarely anything more that simple inserts and selects etc... so I'm no SQL expert. I'm wondering if I could get some help in optimizing a more complex SQL statement that I'm executing on SQLite, from PHP through PDO.

The statement seems to work correctly, just seems to take longer that I would have expected (or perhaps I'm just expecting too much).

This is the 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')
        );

What I've got is a list of subscribers, in one or more groups. I want to add subscribers to a mail queue, selecting those that belong to one or more groups (1,2,3) but exclude those that are also in another set of groups (4,5,6).

Firstly, is the above SQL typical of how to do this?

Secondly, what indicies should I have to make this work as efficiently as possible?

Currently it takes about 30 seconds to get through about 5000 subscriber records (and a handful of groups) on a avg spec LAMP.

At the end of the day performance isn't that critical, but I'd like to understand this stuff better so any insight greatly appreciated.

Brad

Answer 1

Chances are the extra joins are killing you. What if you do:

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

You'd also want to make sure that you have an index on SubscriberGroups( SubscriberID, GroupID )

My guess is that Subscribers already has an index on ID, right?

EDIT: Another option, which may or may not be faster. Look at the query plans of each to see...

This one may be a single index scan which could be faster than two index seeks, but it depends on SQLite's optimizer...

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