Get all items from list table which don't have a successful log entry for 'sent' in log table 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:

list
  id INT
  name VARCHAR

log
  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.
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)  :

SELECT a.*
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 .