Início / dba / Perguntas / 55878
Accepted
dotancohen
Asked: 2014-01-01 03:22:40 +0800 CST

Obtenha todos os itens da tabela de lista que não possuem uma entrada de log bem-sucedida para 'enviado' na tabela de log

  • 772

Como alguém pode recuperar itens que, quando unidos, não possuem um campo específico ( sent) ou, se tiverem esse campo ( sent), também possuem um campo adicional ( failure_reason) para cada sentlinha?

Considere um banco de dados com duas tabelas:

list
  id INT
  name VARCHAR

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

listConteúdo típico :

ID | Name

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

logConteúdo típico :

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

Pode-se ver que List_id 1tem uma entrada de log para ambos enterede sent, sem failure_reason. Assim, este item saiu de minha responsabilidade.

Da mesma forma, List_id 2tem uma logentrada para entered, e duas para sent. Isso ocorre porque o primeiro sentfalhou. Sabemos que o primeiro enviado falhou porque failure_reasonnão é NULL. Como há uma sentlinha bem-sucedida, este item também saiu de minha responsabilidade.

No entanto, List_id 3tem uma logentrada para entered, mas a única sentlinha é uma falha. Assim, este item ainda é de minha responsabilidade e deve ser recuperado na consulta .

Além disso, List_id 4tem uma logentrada para entered, mas nenhuma sentlinha. Assim, este item também continua sob minha responsabilidade e deve ser recuperado na consulta .

Eu tentei usar alguma mágica de subconsulta para obter/excluir linhas com base nos sentvalores, no entanto, como esta tabela está crescendo muito (esperam-se milhares de novos registros por dia), preciso evitar consultas que exijam o retorno de todos os resultados em todo o banco de dados.

Isso está no MySQL 5.1 no CentOS 6.x.

mysql join

1 respostas

  1. Best Answer

    A própria consulta parece ser bastante direta (a partir da amostra de dados fornecida, presumo que um registro com action:sent , failed_reason:NULL seja o estado final para um determinado 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)

    No entanto, se as tabelas forem enormes, o desempenho será prejudicado mesmo se você tiver um índice de cobertura na tabela de log, o que provavelmente não é bom - o nome "log" implica que você está inserindo principalmente dados nela (e inserir deve ser o mais rápido possível) , portanto, cada índice extra anula o objetivo principal desta tabela.
    Parece-me que esse campo de data/data/hora está ausente no modelo; Eu acho que você normalmente deseja tarefas com falha para um intervalo de data específico (presumivelmente relativamente pequeno).

    • 2

relate perguntas