Sebastian's questions

CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

A tabela temporária é criada no início do script com uma coluna "id" auto_increment (e outras). A parte 1 do script preenche a tabela usando vários SELECTs. A parte 2 deve processar todas as linhas selecionadas em blocos de 1.000, mas nunca mais gravará a tabela.

Eu suponho que esses dois comandos são os mesmos

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

mas isso é verdade? Existe alguma chance de que o mySQL recupere as linhas em uma ordem diferente em vários SELECTs, mesmo que a tabela não esteja escrita entre eles?

Classificar a tabela temporária é a parte mais longa durante a leitura e ficaria feliz em me livrar da parte ORDER BY, mas não quero perder nenhum registro porque a ordem mudou entre SELECT LIMIT 0,1000 e SELECT LIMIT 1000, 1000.

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.

Aqui estão as coisas que não posso mudar: Temos uma replicação myISAM do mySQL executando um mestre e alguns escravos. Um escravo está sendo usado para backups noturnos usando mysqldump. Os backups estão usando travas pesadas forçando seu slave a "envelhecer" em relação ao master. mysqldump está se conectando como root despejando todos os bancos de dados enquanto todas as outras conexões estão usando um usuário não-SUPER por banco de dados. Os dumps são iniciados usando um script de shell simples em um servidor de backup dedicado.

Aqui está o problema: gostaria de detectar se um backup está em execução ou não enquanto estou conectado como um usuário "normal" a um dos bancos de dados em execução no servidor que está sendo feito o backup. Os Cronjobs devem ser capazes de reagir ao backup em execução saindo antecipadamente ou processando apenas os trabalhos necessários realmente importantes que não podem esperar até que o backup seja concluído. O servidor que executa o script de backup não tem conexões com os servidores da web, seu único ponto comum é o servidor mysql. O script de backup pode fazer algumas coisas para sinalizar o servidor como "atualmente executando um backup". Prefiro definir algum tipo de "variável de ambiente global do lado do servidor visível para todos os usuários" para ignorar apenas o único escravo executando o backup, mas também seria bom simplesmente saber que um backup está sendo executado naquele banco de dados ou cadeia de replicação .

Tentei criar uma tabela temporária no início do backup (existente = o backup está em execução, não existente = nenhum backup em execução), mas as tabelas temporárias aparecem apenas para a conexão que a criou originalmente (como esperado). Pesquisei no Google se um usuário não SUPER poderia obter permissão para ver o SHOW PROCESSLIST de todas as conexões, incluindo outros usuários e root (qualquer conexão do servidor de backup = o backup está em execução) - mas sem sorte. Dar SUPER a todos os usuários do banco de dados não é uma opção.

Alguma sugestão?

Obrigado por ler (e responder).