Mysql: consulta lenta

[alxsts]

Olá!

Realmente vc tem razão, não são todos que precisam ser considerados nesta consulta.

Será que precisa mesmo todas as cotações de todos os tempos? Cotações antigas só vão servir como perda de tempo, deveria limitar data também.

[cjp]

De fato ficou contraditório.
Mas o fato é que eu ainda preciso definir bem o que realmente é necessário.
Veja, neste primeiro caso (marca='V')

select codigo,max(datahora) as data,nritem from acoes use index (coddt) where marca='V' and hrvisto<>91234 and datahora>'"+dtsql(date()-20)+"' group by codigo order by data

o mais provável é que seja suficiente trazer os dados dos últimos 20 dias (talvez até menos). Mas não será sempre assim. É possível que não tenha nenhuma cotação cadastrada nos últimos 20 dias (ou até em período bem maior), e mesmo assim o código deve constar na lista (aliás, neste caso, com maior razão).
Ainda não pensei num critério ideal para restringir a pesquisa.
O que eu preciso é de uma lista simples dos códigos que não tenham cotação cadastrada nos últimos minutos ou nas últimas horas (dependendo da marca).
Antes a consulta estava mais complexa, verificando isso no próprio select. Agora eu simplifiquei o select, como acima, e verifico localmente depois a hora da última cotação.
Como poderia melhorar isso?

[JoséQuintas]

Como poderia melhorar isso?

Com uma boa modelagem de dados.

Apenas talvez:

Código: Selecionar todos

SELECT a.codigo, max( acoes.datahora )  AS data, acoes.nritem
FROM
   (
      SELECT
        ( SELECT DISTINCT CODIGO FROM ACOES WHERE MARCA='V' ) AS A
      LEFT JOIN ACOES
      ON A.CODIGO = ACOES.CODIGO AND HRVISTO <> 91234 AND DATAHORA > '2021-08-01' 
   )
WHERE .....
GROUP BY a.codigo

apenas um simulado meio equivalente

[cjp]

Testei o teu exemplo no Heidi, e deu erro, conforme anexo.
Tirei o Where porque não saberia o que colocar ali. Não sei se foi por isso o erro, mas acho que não.
A verdade é que não entendi bem o teu exemplo. Pra mim ficou confuso com 3 select. Também não sei para que serviriam o left join.
Note que antes eu tinha um select parecido com este, mas eram 2 select, que está no início deste post. E me foi recomendado simplificá-lo.

[cjp]

Pessoal, retomo este post porque, em primeiro lugar, consegui resolver o problema inicialmente aqui relatado. Mudei totalmente a tabela e a programação, o que eliminou totalmente o problema de consultas lentas aqui mencionado. Demorou, mas resolvi isso. De quebra, resolvi também um outro problema que ocorria nesta mesma tabela, que foi relatado em outro post (depois vou noticiar isto também lá).
Mas, em segundo lugar, agora comecei a receber outro relatório de consulta lenta do mesmo provedor, desta vez em outra tabela, e mais incompreensível.

A query apontada como lenta é esta:

Código: Selecionar todos

DELETE FROM ativ WHERE acao = 'Exqado' AND DATA <= '2024-09-16'

Digo incompreensível porque é uma query simples. Como seria possível tornar esta query mais rápida?

Esta é uma tabela grande (neste momento ela está com 2,1 GB, mas varia de 1 a 3 GB), com muitos registros (neste momento com 1.131.800), mas funciona bem. Está com os índices necessários.

Imagino que seria possível fazer deletes parciais (ex: um delete para cada data). Isso deixaria cada query mais rápida, mas no total, todas juntas, ficaria bem mais lento. Faria sentido?

Existe algum outro jeito de resolver isso?

É razoável o provedor ficar reclamando de querys lentas neste caso? No meu modo de ver, 2 GB não é um tamanho excessivo para uma tabela MySQL. Estou errado?

[dbsh]

Qual dos dois tem índice ação, data ou nenhum?
Qual dos dois tem menor ocorrência na tabela ação ou data?
Crie índice do que geralmente tem menor ocorrência.

EX: acao = 'Exqado' (10000 registros), DATA <= '2024-09-16' (1000 registros) // aqui seria vantagem cria índice para data

[dbsh]

saber o total das ocorrências:

Código: Selecionar todos

SELECT  COUNT(*) FROM ativ WHERE  acao = 'Exqado';
SELECT  COUNT(*) FROM ativ WHEREDATA <= '2024-09-16;

[cjp]

Os dois têm índices.
Neste momento tem 890.000 registros do primeiro, e 142.000 do primeiro.

[JoséQuintas]

Pelo jeito usa muito isso.
Cria um índice por acao, data
Isso é diferente de criar dois índices, um pra cada.

Apenas compare com DBF:
Como seria mais rápido em DBF ?
SEEK da ação, e datas em ordem crescente, assim não precisaria processar tudo.
Se no DBF é mais rápido assim, no SQL também vai ser.
Apenas crie o índice que o SQL se vira.

[dbsh]

Use EXPLAIN no inicio da sua query para verificar como esta sendo feita, EX:
EXPLAIN DELETE FROM ativ WHERE acao = 'Exqado' AND DATA <= '2024-09-16'

O comando EXPLAIN não ira executar o delete, ele verifica a performance do seu comando.

vai aparecer o campo possible_keys e key
possible_keys = possíveis indices a ser utilizado
key = índice usado
senão aparecer índice em key é porque não esta usando índice, revise seus índices que tem algo errado.

[dbsh]

Tome os cuidados:
se seu campo é caractere e você faz a pesquisa com numero e provável que não vá fazer a pesquisa usando o índice, supondo que você tenha um campo GTIN, caracter e um índice para ele, EX:.

select * from produto where gtin = 5;
a pesquisa será incremental

um indice CREATE INDEX nome_do_indice ON ativ(acao, data);
não vai te ajudar se você tiver que pesquisar só por data, mas vai agilizar muito a pesquisa se você sempre usar os dois campos(acao,data).

[not] é opcional.
Não use SELECT * from ativ where [not] in (select...)
Prefira SELECT * from ativ [not] EXISTS (select...)

[cjp]

Mas o índice já está assim.
Veja o anexo, com o explain.
Não está certo?

[dbsh]

Usa um gerenciador de banco de dados (estou usando o HeidiSQL) e mostra como esta criando os índices.

[cjp]

Código: Selecionar todos

CREATE TABLE `ativ` (
	`USUARIO` VARCHAR(3) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`PROGRAMA` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`DATA` DATE NULL DEFAULT NULL,
	`HORA` CHAR(8) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`ACAO` VARCHAR(35) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`detalhes` VARCHAR(6000) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`NRTAREFA` VARCHAR(12) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`PRIORIDADE` INT(1) NULL DEFAULT NULL,
	`CAMPO` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`TEMPOUSADO` DECIMAL(6,1) NULL DEFAULT NULL,
	`TEMPOCOMPU` DECIMAL(6,1) NULL DEFAULT NULL,
	`TEMPOPAGO` DECIMAL(6,1) NULL DEFAULT NULL,
	`assunto` VARCHAR(45) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`maquina` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`marca` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `data` (`DATA`) USING BTREE,
	INDEX `usuario` (`USUARIO`) USING BTREE,
	INDEX `dataacao` (`DATA`, `ACAO`) USING BTREE,
	INDEX `acao` (`ACAO`) USING BTREE,
	INDEX `consativ` (`USUARIO`, `HORA`, `ACAO`, `NRTAREFA`, `assunto`) USING BTREE,
	INDEX `listaftp` (`detalhes`(767), `USUARIO`, `DATA`) USING BTREE,
	INDEX `prior` (`USUARIO`, `DATA`, `ACAO`) USING BTREE,
	INDEX `tpc` (`TEMPOCOMPU`, `id`) USING BTREE,
	INDEX `ação+data` (`DATA`, `ACAO`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=148367111
;

[dbsh]

Aparentemente esta correto "mas", estes dois índices estão repetidos:
INDEX `dataacao` (`DATA`, `ACAO`) USING BTREE,
INDEX `ação+data` (`DATA`, `ACAO`) USING BTREE

Você não precisa deste índice: INDEX `data` (`DATA`) USING BTREE.
Quando você pesquisa por data, o MySQL usará o índice `dataacao`, o campo data é o o primeiro na ordem (`DATA`, `ACAO`), mesmo que você não tenha o campo acao no filtro WHERE.

A mesma coisa. Pode sair o INDEX `usuario` (`USUARIO`) USING BTREE, pode ficar o índice: INDEX `prior` (`USUARIO`, `DATA`, `ACAO`) USING BTREE,

Refaça seus índices, muito índices atrapalha a performance do banco de dados: INSERT/UPDATE/DELETE

Se você quer ver mais informação no EXPLAIN use:
EXPLAIN FORMAT=json DELETE .....

Crie seus índices, posicionando os campos na sequencia mais usadas,
EX: INDEX `consativ` (`USUARIO`, `HORA`, `ACAO`, `NRTAREFA`, `assunto`) USING BTREE,
SELECT * FROM ativ usuario='001' and nrtarefa='123456789', não usaria o índice `consativ`, e sim o índice `prior`, faltou os campos hora e acao
SELECT * FROM ativ usuario='001' and hora='18:00:00' and acao='alguma-coisa', usaria o índice `consativ`.

Para forçar o uso de um índice específico, pode-se usar a sintaxe.
SELECT * FROM ativ FORCE INDEX (dataacao) WHERE .........

O MySQL não usa de índices nos seguintes cenários(deve ter mais):
Comparação de colunas de tipos diferentes.
Tamanhos e conjuntos de caracteres em uma cláusula WHERE ou junção.
A tabela é muito pequena, uma busca sequencial é mais rápida.
A tabela é grande, mas a consulta requer a maioria ou todas as linhas, EX: campos com valores S/N apenas.

O MySQL seleciona o melhor índice para uma query de acordo com a cardinalidade da coluna, que é o número de valores distintos que ela contém.
Os índices funcionam melhor em colunas com uma cardinalidade relativamente alta em relação ao número de linhas da tabela.