Início / dba / Perguntas / 13581
Accepted
Sebastian
Asked: 2012-02-22 08:18:00 +0800 CST

Limite WHERE a MAX() & COUNT()

  • 772

Esta é a minha consulta mySQL atual:

SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread
  FROM TAB_EVENT e
  LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id
  WHERE ( m.`read` IS NULL OR m.`read` = 0)
  GROUP BY e.id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;

Eu recebo: Todas as linhas TAB_EVENT que não possuem nenhuma mensagem TAB_MESSAGE ou possuem pelo menos uma não lida (lida = BOOLEAN DEFAULT 0).

Eu preciso: Todas as linhas TAB_EVENT, independentemente de suas mensagens E a data e hora das mensagens não lidas mais recentes e a contagem de mensagens não lidas para cada TAB_EVENT.

Pode haver milhares de linhas TAB_EVENT e muito mais linhas TAB_MESSAGE e gostaria de evitar a execução de uma subconsulta para cada evento único.

Alternativa 1: Usando UNION usando a consulta acima mais outra com m. read= 1. Desvantagem: Duas consultas pesadas com JOINed mais outra tabela temporária.

Alternativa 2: SELECT e.* incondicional mais outro SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() e mesclar tudo dentro do script Perl de chamada. Desvantagem: Perder a capacidade de um LIMIT do lado do servidor.

ATUALIZAÇÃO/RESOLUÇÃO

Aqui está minha consulta final fazendo o trabalho:

SELECT e.*, m.unread_last, m.unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT event_id,MAX(`datetime`) AS unread_last, COUNT(*) AS unread FROM TAB_MESSAGE
    WHERE `read` = 0
    GROUP BY event_id
    ORDER BY `datetime` DESC
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC
LIMIT 10;

A condição de leitura IS NULL capturou eventos sem nenhuma mensagem na consulta original, a coluna de leitura é booleana NOT NULL.

Esta consulta agora retorna todos os eventos e adiciona o carimbo de data/hora da mensagem não lida mais o número de mensagens não lidas. Ambas as colunas de mensagem são NULL para eventos que não possuem nenhuma mensagem, o que é perfeitamente correto (será traduzido como "" para data e hora e 0 para a contagem no lado Perl).

Graças a RolandoMySQLDBA, minha consulta é baseada em sua resposta.

mysql join

2 respostas

  1. Tente ingressar em uma subconsulta:

    SELECT e.*, m.datetime AS unread_last, m.id AS unread
  FROM TAB_EVENT e
  LEFT JOIN (
    SELECT event_id, MAX(datetime) AS `datetime`, COUNT(id) AS id
    FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    GROUP BY event_id
  ) m ON e.id=m.event_id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;
    • 3
  2. Best Answer

    Aqui está sua consulta original da pergunta

    SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM TAB_EVENT e 
LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id 
WHERE ( m.`read` IS NULL OR m.`read` = 0) 
GROUP BY e.id 
ORDER BY m.datetime DESC, e.id ASC 
LIMIT 10;

    Talvez tente refatorar a consulta de forma que seja executada nesta sequência

    • colete apenas as colunas necessárias de TAB_MESSAGE
    • aplique LIMIT 10 nas linhas coletadas de TAB_MESSAGE
    • executar o JOIN
    • aplique MAX() e COUNT() por último

    Aqui está o que estou propondo

    SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
LEFT JOIN TAB_EVENT e 
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

    De uma chance !!!

    ATUALIZAÇÃO 2012-02-21 17:06 EDT

    SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

    @Sebastian, coloquei a consulta de volta na ordem de junção original. Por favor, tente isso também !!!

    ATUALIZAÇÃO 2012-02-21 17:11 EDT

    Verifique se o campo datetime está indexado

    ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);
    • 2

relate perguntas