Query Lenta

[Amadeufm 0]

boas,

 

tou tendo problemas com uma query.

 

é o seguinte eu estou usando o mysql e uma das query é lenta, e quando meto essa mesma query no Acess ela é super rápida,

Alguem me sabe responder pk?

 

Existe solução pra por o mysql mais rápido.

[cassitos 2]

Oi,

o Mysql tem um comando chamado EXPLAIN, dá uma olhada neste documento http://dev.mysql.com/doc/refman/5.0/en/explain.html e aqui http://dev.mysql.com/doc/refman/5.0/en/using-explain.html. Pode te ajudar a saber o que está acontecendo com o SELECT.

Pode ser que a consulta não esteja utilizando os índices certos (se tiver índices criados). A partir da informação, poderá criar os índices para agilizar a sua consulta e verificar se tais índices estão sendo realmente utilizados.

O uso é relativamente simples, ex:

EXPLAIN SELECT * FROM tabela WHERE [condições]

[Amadeufm 0]

vou postar o minha query e com o explain, se alguem me conseguir ajudar:

SELECT

TbLinhas.IdLinha, TbLinhas.NomeLinha, TbProdutos.IdProduto, TbProdutos.NomeProduto, TbFerramentas.IdFerramenta, TbFerramentas.NomeFerramenta, TbFerramentas.ClasseFerramenta, TbAssocProdutosComLinhas.IdProdutoLinha, TbAssocFerramentasComLinhas.IdFerramentaLinha, TbAssocProdutosComFerramentas.IdProdutoFerramenta, TbOrdensManutencao.IdOrdemManutencao, TbOrdensManutencao.NomeOrdemManutencao, TbOrdensManutencao.IntervaloTempoEntreManutencoes, TbOrdensManutencao.ToleranciaTempoEntreManutencoes, TbOrdensManutencao.IntervaloProducaoEntreManutencoes, TbOrdensManutencao.ToleranciaProducaoEntreManutencoes, TbOrdensManutencao.TempoExecucao, TbOrdensManutencao.DataUltimaManutencao, TbOrdensManutencao.ProducaoUltimaManutencao, TbOrdensManutencao.ProducaoActualManutencao

 

FROM TbProdutos

 

INNER JOIN (TbLinhas

INNER JOIN (TbFerramentas

INNER JOIN (TbAssocProdutosComLinhas

INNER JOIN ((TbAssocFerramentasComLinhas

INNER JOIN TbAssocProdutosComFerramentas

ON TbAssocFerramentasComLinhas.IdFerramentaLinha = TbAssocProdutosComFerramentas.IdFerramentaLinha)

INNER JOIN

TbOrdensManutencao

ON TbAssocFerramentasComLinhas.IdFerramentaLinha = TbOrdensManutencao.IdFerramentaLinha)

ON TbAssocProdutosComLinhas.IdProdutoLinha = TbAssocProdutosComFerramentas.IdProdutoLinha)

ON TbFerramentas.IdFerramenta = TbAssocFerramentasComLinhas.IdFerramenta)

ON (TbLinhas.IdLinha = TbAssocProdutosComLinhas.IdLinha)

AND (TbLinhas.IdLinha = TbAssocFerramentasComLinhas.IdLinha))

ON TbProdutos.IdProduto = TbAssocProdutosComLinhas.IdProduto

WHERE (((TbLinhas.NomeLinha)='L06')

AND ((TbProdutos.NomeProduto)='7647239460'));")

 

o Explain é o seguinte

 

Select_type Id table type Possible_keys key Key_len ref rows extra

simple 1 tbordemmanutencao ALL NULL null null null 1051

simple 1 tbassocProdutosComFerramentas ALL NULL null null null 22872 Using where

simple 1 TbAssocProdutosComLinhas Eq_ref PRIMARY Primary 4 nomedabasededados+table 1

simple 1 tbProdutos Eq_ref PRIMARY Primary 4 nomedabasededados+table 1 Using where

simple 1 TbLinhas Eq_ref PRIMARY Primary 4 nomedabasededados+table 1 Using where

simple 1 TbAssocFerramentasComLinhas Eq_ref PRIMARY Primary 4 nomedabasededados+table 1 Using where

simple 1 tbFerramentas Eq_ref PRIMARY Primary 4 nomedabasededados+table 1

[cassitos 2]

hummm

o type indica o tipo de pesquisa feita na tabela (os possíveis valores estão na documentação).

o possible keys representa possíveis índices que podem ser utilizado pelo mysql para otimizar a consulta.

a key é o índice utilizado pelo banco na consulta.

o ref é a referência utilizada na composição do índice utilizado.

o rows representa a quantidade de registros que estão sendo 'lidos' (mesmo que não seja tudo isto que será retorando no resultado).

o extra informa alguma informação adicional sendo feita na consulta desta tabela.

tudo isto (e muito mais) está aqui http://dev.mysql.com/doc/refman/5.0/en/using-explain.html.

 

bom, de acordo com o resultado do explain, as tabelas tbordemmanutencao e tbassocProdutosComFerramentas, não estão com nenhum índice utilizado, e o banco sequer conseguiu identificar algum índice que pudesse ser utilizado.

 

um dos mais importante para verificar o desempenho neste resultado é a coluna rows:

tbordemmanutencao está tendo 1051 registros lidos e a tbassocProdutosComFerramentas está com 22872.

 

daí você tem que pegar estes rows e multiplicar (cardinalidade), neste caso: 1051 x 22872 x 1 x 1 x 1 x 1 = 24038472. vai processar este tanto de registros, se não me engano. e derrepente nem vai retornar nem 1% deste total de registros.

 

então para otimizar a consulta, você tem que verificar a estrutura destas tabelas e ver se tem algum índice nelas, se tiver, você tem que revisar seu SQL e ver o que faltou (ou o que terá de mudar) para que os índices sejam utilizado, pode ser que você tenha que criar os devidos índices.

 

quando o type está ALL, isto é muito ruim, pois estará lendo TODOS os registros da tabela em questão, o que nesta caso acho que não está correto.

 

bom, é assim que resolvo meus problemas de consultas lentas.

 

esta documentação tem todas as informações que você precisa, só que em inglês.

 

qquer dúvida posta ae p/ galera.

[Amadeufm 0]

obrigado por me responder, mas eu nao consigo perceber o porque de uma base de dados em acess esta query ser rapida (1 seg.) ja com a base de dados em mysql e a mesma query leva me cerca de 7segundos...

 

 

o que é que terei que mudar mais concretamente? é que sou novat nas querys e tou desesperado!!!

[Amadeufm 0]

obrigado por me responder, mas eu nao consigo perceber o porque de uma base de dados em acess esta query ser rapida (1 seg.) ja com a base de dados em mysql e a mesma query leva me cerca de 7segundos...

 

 

o que é que terei que mudar mais concretamente? é que sou novat nas querys e tou desesperado!!!

[cassitos 2]

bom, quanto a diferença entre os bancos prefiro não aprofundar, deixo p/ outro que possa responder com mais precisão.

 

quanto a lentidão... dá uma olhada na tabela TbOrdensManutencao se tem algum índice para a coluna IdFerramentaLinha. e veja se a tabela TbAssocProdutosComFerramentas tem algum índice para a coluna IdProdutoLinha.

se não tiver, crie os índices para estes campos, execute o EXPLAIN novamente para verificar se o mysql consegue utiliza-los, e se a quantidade no ROWS diminui.

 

você tem permissão para alterar tabelas? vai precisar.

 

posta o resultado após a criação dos índices p/ gente acompanhar.

[ska_ska 0]

melhor coisa.. faz um diagramazinho basico dessas tabelas.. ahuhau.. ta confuso de entender esses joins.. naum por serem complexos, mas pela escrita mesmo. Entendendo melhor o relacionamento das tabelas, fica mais facil de ajudar.

 

Mas as dicas do cassitos, guru do forum, concerteza pode te ajudar.

 

Informe também o volume medio de dados em cada tabela para que possamos verificar a forma melhor de fazer e otimizar, ok

 

abs,

ska!

[ma®©elo 0]

Fala, galera!!!

 

Desculpem reabrir esse tópico, mas é q estou com um problema de performance de um sistema q "herdei", agora tenho q consertar algumas coisas...

 

Vi q ele tinha uma SQL assim:

 

select NTC.notTitulo,								
NTC.notChamada,
NTC.notConteudo,
NTC.notData,
NTC.notPalavraChave,
NTC.notVideo,
NTC.notTipo,
NTC.notRedirect,
NTC.notURL,
FNT.fntNome,
SEC.secCodigo,
SEC.secNome,
SEC.secUrl,
(SELECT dstCodigo FROM tbnoticiadestaque WHERE (notCodigo = NTC.notCodigo)) AS dstCodigo,
notBloquearComentarios
FROM tbnoticia AS NTC 
INNER JOIN tbfontenoticia AS FNT ON NTC.fntCodigo = FNT.fntCodigo 
INNER JOIN tbsecao AS SEC ON NTC.secCodigo = SEC.secCodigo 
WHERE (notStatus = 1) AND (secStatus =1) AND (NTC.notCodigo = 32918)

O explain retornou:

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	NTC 	const 	PRIMARY,fntCodigo,secCodigo 	PRIMARY 	8 	const 	1 	 
1 	PRIMARY 	FNT 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	 
1 	PRIMARY 	SEC 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	 
2 	DEPENDENT SUBQUERY 	tbnoticiadestaque 	ref 	ind_tbnoticiadestaque 	ind_tbnoticiadestaque 	8 	const 	1

Pensei em substituir o subquery por um left join:

SELECT NTC.notTitulo,
NTC.notChamada,
NTC.notConteudo,
NTC.notData,
NTC.notPalavraChave,
NTC.notVideo,
NTC.notTipo,
NTC.notRedirect,
NTC.notURL,
FNT.fntNome,
SEC.secCodigo,
SEC.secNome,
SEC.secUrl,
DST.dstCodigo,
notBloquearComentarios
FROM tbnoticia AS NTC 
INNER JOIN tbfontenoticia AS FNT ON NTC.fntCodigo = FNT.fntCodigo 
INNER JOIN tbsecao AS SEC ON NTC.secCodigo = SEC.secCodigo 
LEFT JOIN tbnoticiadestaque DST ON DST.notCodigo = NTC.notCodigo
WHERE (notStatus = 1) AND (secStatus =1) AND (NTC.notCodigo = 32918)

Pensei em obter o mesmo resultado, mas com uma query q eu achava ser mais eficiente. Só q olhem só o resultado do explain:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	NTC 	const 	PRIMARY,fntCodigo,secCodigo 	PRIMARY 	8 	const 	1 	 
1 	SIMPLE 	FNT 	const 	PRIMARY 	PRIMARY 	8 	const 	1 	 
1 	SIMPLE 	SEC 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	 
1 	SIMPLE 	DST 	ref 	ind_tbnoticiadestaque 	ind_tbnoticiadestaque 	8 	const 	1

Alguém pode me explicar o explain??? :blink:

 

Pq olho, olho e não vejo diferença entre os dois resultados dos explains, a não ser o code, q no primeiro era 2, e no segundo, foi incorporada à 1... O q quer dizer isso? Há alguma vantagem na mudança q quero fazer???

 

[ ]s

[giesta 29]

A segunda deve ser mais rapida devido a ausencia de subquery e a possibilidade de chave(mesmo q ele nao use)... mas soh fazendo o teste pra ver

[ma®©elo 0]

A segunda deve ser mais rapida devido a ausencia de subquery e a possibilidade de chave(mesmo q ele nao use)... mas soh fazendo o teste pra ver

Valeu pela resposta!!! Vou testar!!!

 

Mas uma coisa q eu achei curiosa é q os tempos de resposta foram exatamente iguais: 0.0009 segundos :wacko:

[giesta 29]

o q determina mesmo a velocidade eh a quantidade de linhas possiveis q nesse caso sao iguais, no momento a segunda eh melhor, pois mesmo usando SIMPLE (lambendo o banco todo) ela deve ser mais rapida por nao ter a subquery, caso a quantidade de linhas aumente mto a primeira pode ser melhor caso o MySQL continue usando SIMPLE em vez de usar PRIMARY... isso eh tudo coisa do otimizador de query dele, q nem sempre funciona bem

[ma®©elo 0]

o q determina mesmo a velocidade eh a quantidade de linhas possiveis q nesse caso sao iguais, no momento a segunda eh melhor, pois mesmo usando SIMPLE (lambendo o banco todo) ela deve ser mais rapida por nao ter a subquery, caso a quantidade de linhas aumente mto a primeira pode ser melhor caso o MySQL continue usando SIMPLE em vez de usar PRIMARY... isso eh tudo coisa do otimizador de query dele, q nem sempre funciona bem

Ok, giesta, valeu pelas dicas...

 

Uma outra pergunta: O SIMPLE é indicativo de q ele lambe o banco todo? Tem como melhorar isso através dos meus índices ou refazendo a query???

 

Mais outra: O q seria o otimizador de queries dele? Tem como a gente intereferir via programação nisso???

 

[ ]s