Jeffrey's questions

Consultas recursivas para encontrar a estrutura de árvore (pai-filho) em uma tabela são comumente encontradas aqui, no entanto, a maioria tem o problema de que o childID deve ser maior que o parentID.

Uma possível solução para isso é usar uma consulta como (dado o pai X, encontre todos os filhos)

SELECT * FROM (
    SELECT @pv:=(
        SELECT GROUP_CONCAT( child_id SEPARATOR "," ) FROM link_parent_child WHERE FIND_IN_SET( parent_id, @pv )
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$parentStartID$) tmp
    ) a
WHERE lv IS NOT NULL

OU Dado o filho X, encontre todos os pais

SELECT * FROM (
    SELECT @pv:=(
        SELECT parent_id FROM link_parent_child WHERE child_id = @pv
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$ChildStartID$) tmp
    ) a
WHERE lv IS NOT NULL

No entanto, com uma tabela de mais de 500 mil registros, isso é um pouco lento... (eufemismo)

CREATE TABLE `link_parent_child` (
    `parent_id` int(11) NOT NULL,
    `child_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `link_parent_child`
    ADD PRIMARY KEY (`child_id`,`parent_id`);
    ADD KEY `child_id` (`child_id`),
    ADD KEY `parent_id` (`parent_id`),
    ADD KEY `parent_id_2` (`parent_id`,`child_id`);

mesmo com índices definidos e apenas 10 mil registros, a consulta demora mais de 80 segundos para ser executada.

Portanto, esta consulta (corrija-me se estiver errado) é tão lenta porque a parte interna retorna NULL para quase todas as linhas, que (eu acho) são armazenadas em uma tabela temporária e filtradas pela parte externa da consulta. Eu acho que a consulta seria acelerada se os NULLs fossem filtrados anteriormente, mas isso é possível?

Minha árvore tem um comprimento dinâmico, mas principalmente não mais profundo do que 3-10 camadas. Devo pegar minhas perdas e resolver isso programaticamente, ou existe um método para acelerar isso?

Tenho tentado otimizar minha mesa, mas ainda não consigo obter os resultados desejados.

CREATE TABLE IF NOT EXISTS `puzzles` (
  `puzzleID` int(7) NOT NULL,
  `puzzleMask` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `puzzleScore` int(7) NOT NULL,
  `time` int(11) NOT NULL DEFAULT '0',
  `hour` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Iip` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`puzzleID`, `puzzleMask`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

A combinação de ID e Mask deve ser única, enquanto ID é um número inteiro aleatório e Mask uma combinação de números com comprimento máximo de 30. Hora é o registro de data e hora unix e hora é "time - (time % 3600)" (a hora em qual a linha foi enviada). ip é o endereço IP de um servidor (cerca de 20 servidores) e Iip é INET_ATON( ip ).

Esses 20 servidores enviam informações de quebra-cabeça para esse banco de dados. Gostaria de mostrar estatísticas ao vivo das informações enviadas (5 a 10 registros por segundo).

Para isso, utilizo duas consultas principais que precisam ser as mais otimizadas possíveis:

Obter estatísticas totais

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# GROUP BY hour

Obtenha estatísticas dependentes do servidor

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# AND Iip = INET_ATON("#IPAddress#") GROUP BY hour

Minha pergunta é, quais índices usar? Eu tentei muitas coisas agora, mas com o banco de dados aumentando rapidamente, o tempo de tentativa e erro acabou... por exemplo, preciso de um índice separado para o puzzleScore, já que quero o máximo? ou preciso combiná-lo em outro índice?

Além disso, é verdade que usar uma chave primária (puzzleID e puzzleMask) em combinação com INSERT IGNORE INTO... ?

obrigada

Dado um simples, com uma coluna de texto e hora, desejo selecionar X valores exclusivos da coluna de texto, enquanto essa linha contém o valor mais alto de hora.

INSERT INTO `test` (`id`, `text`, `time`) 
VALUES
    (1, 'test1', 1),
    (2, 'test1', 3),
    (3, 'test1', 2),
    (4, 'test2', 1),
    (5, 'test2', 100),
    (6, 'test2', 20) ;

A consulta que atende a maioria dos meus requisitos é:

SELECT a.* FROM
    test a
INNER JOIN (
    SELECT `text`, MAX(`time`) AS `time`
    FROM
        test
    WHERE
        `text` LIKE "te%"
    GROUP BY
        `text`) b
ON
    a.`text` = b.`text` AND
    a.`time` = b.`time`
ORDER BY
    a.`text` ASC
LIMIT 0,50

Para mesas pequenas, isso funciona perfeito. Embora na minha tabela (mais de 300k linhas), isso faça o mysql travar, devido à subconsulta.

É possível otimizar esta consulta? Se não puder ser otimizado, seria possível selecionar os últimos valores exclusivos inseridos para text? (os ide timesão teoricamente não correlacionados, embora em 99% dos casos uma correlação seja encontrada, enquanto quanto maior o id, maior o tempo)

Quero criar usuários automaticamente, todos com um ID exclusivo.

Agora aqui está o problema. Se dois usuários forem criados exatamente no mesmo nanossegundo, isso pode não causar problemas. Assim, o que eu faço agora é:

1. Obtenha o próximo número de Autoincremento
2. Adicione um novo usuário com o nome "U" + número de autoincremento + random(0-9)

Quando mais de 10 usuários são criados no mesmo nanossegundo, isso ainda causará problemas (em teoria!). Portanto, estou procurando uma maneira de permitir que o MySQL lide com o nome de usuário.

Considere a seguinte tabela:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Eu quero usar:

INSERT INTO test (id, name) VALUES (null, "U" + id)

onde id é o número de autoincremento. Mas, como o id não está definido no MySQL, ele conterá 0.

Como definir id, para que contenha o número de autoincremento dessa linha. Isso pode ser feito facilmente por duas consultas, mas estou procurando fazer isso em apenas uma consulta :)

Obrigado!