How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent row?

Consider a database with two tables:

  id INT
  name VARCHAR

  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

Typical list contents:

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

Typical log contents:

ID | List_id | Action  | Failure Reason
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

It can be seen that List_id 1 has a log entry for both entered and sent, with no failure_reason. Thus, this item has left my responsibility.

Likewise, List_id 2 has a log entry for entered, and two for sent. This is because the first sent failed. We know that the first sent failed as the failure_reason is not NULL. Since there is a successful sent row, this item has also left my responsibility.

However, List_id 3 has a log entry for entered, but the only sent row is a failure. Thus, this item is still my responsibility and should be retrieved in the query.

Additionally, List_id 4 has a log entry for entered, but no sent row. Thus, this item is also still my responsibility and should be retrieved in the query.

I have tried using some subquery magic to get / exclude rows based on the sent values, however as this table is growing huge (expected thousands of new records per day) I need to avoid queries that require returning all results in the entire database.

This is in MySQL 5.1 on CentOS 6.x.

1 Answer 1


Query itself seems to be quite straightforward (from data sample provided I assume that a record with action:sent , failure_reason:NULL is the final state for a given list_id) :

FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

However, if tables are huge , performance will suffer even if you have a covering index in log table which is probably not good - name "log" implies that you are mostly inserting data into it (and insert has to be as fast as possible), so each extra index defeats the main purpose of this table.
It looks to me though that date/datetime field is missed in the model; I guess you typically want failed tasks for a particular (presumably relatively small) date interval .

  • An index on (list_id) - which I guess is already there - and one on (action, failure_reason, list_id) will help. Commented Dec 31, 2013 at 16:26
  • @ypercube : I guess it's also save to have index on (list_id,action,failure_reason) and drop the existing one on list_id.
    – a1ex07
    Commented Dec 31, 2013 at 17:32
  • yes, list_id first, then action and failure_reason.
    – Erik Hart
    Commented Dec 31, 2013 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.