Início / dba / Perguntas / 43837
Accepted
Jeffrey
Asked: 2013-06-05 14:24:46 +0800 CST

Selecione um valor exclusivo enquanto o tempo é mais alto da maneira mais ideal

  • 772

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)

mysql-5.5

1 respostas

  1. Best Answer

    O índice óbvio para esta consulta está ativado (test,time). Adicione com:

    ALTER TABLE test
  ADD INDEX text_time_IX          -- choose a name for the index
    (test,time) ;

    Você também pode obter ganho de eficiência se usar esta versão da consulta (empurrando o limite dentro da tabela derivada):

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

    Se houver casos em que o mesmo máximo timeapareça em mais de uma linha com o mesmo text, ele não mostrará os mesmos resultados exatos e retornará mais de 50 linhas (mas acho que esta versão é mais apropriada). Se você realmente deseja a escolha arbitrária em caso de empate, também pode adicionar o LIMITna seleção externa.

    • 1

relate perguntas