Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 .

share|improve this answer
    
An index on (list_id) - which I guess is already there - and one on (action, failure_reason, list_id) will help. –  ypercube Dec 31 '13 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 Dec 31 '13 at 17:32
    
yes, list_id first, then action and failure_reason. –  Erik Hart Dec 31 '13 at 21:25
    
Thank you very much! –  dotancohen Jan 6 at 9:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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