Ir para conteúdo
ndias

Otimização de Query MySql - Using temporary; Using filesort

Recommended Posts

Alguém pode me ajudar?

 

Tenho a seguinte query:

select cc.CampoConhecimento, COUNT(distinct(cc.idCv)) as Total, 
count(CASE WHEN cc.NivelConhecimento = 1 OR cc.NivelConhecimento = 0 OR cc.NivelConhecimento IS NULL THEN 'Basico' ELSE NULL END) as Basico, 
count(CASE WHEN cc.NivelConhecimento = 2 THEN 'Intermediario' ELSE NULL END) as Intermediario, 
count(CASE WHEN cc.NivelConhecimento = 3 THEN 'Avancado' ELSE NULL END) as Avancado 
from tblCurriculoConhecimento cc 
inner join tblCadastro_Curriculo_Incompleto cv  on cv.IdCV = cc.idCV 
where cv.Ativo = 'S'
and (cv.Sexo = 'M' or cv.Sexo = 'F') 
and cv.Excluido = 'N' 
and ((YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4) >= 16 or (datediff(YEAR(ifnull(date(cv.DataUpdate),date(cv.DataCadastro))),CURDATE()) + YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4)) >= 16) 
and (YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4) <= 97 or (datediff(YEAR(ifnull(date(cv.DataUpdate),date(cv.DataCadastro))),CURDATE()) + YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4)) <= 97)) 
group by cc.CampoConhecimento order by cc.CampoConhecimento asc;

Que em 40s me retorna isso:

Access	53593	42482	8937	2174
ASP	13271	12963	241	67
AutoCad	20740	15637	3335	1768
C	15261	13903	1093	265
CMais	15666	14284	1118	264
Cobol	12826	12670	109	47
CorelDraw	22418	18069	3192	1157
CSS	33043	27538	4350	1155
Dbase	13049	12839	167	43
Delphi	13226	12901	248	77
Dreamweaver	16216	14578	1223	415
Excel	110420	50002	43771	16647
Flash	17492	15713	1370	409
HTML	50819	38377	9210	3232
Java	15566	14204	1092	270
JavaScript	37677	31640	4869	1168
Linux	19782	17101	2141	540
Macintosh	15491	14245	854	392
MSSQLServer	32612	28300	3625	687
MySQL	32499	27919	3786	794
Net	13734	13095	472	167
Oracle	13711	13240	400	71
Outlook	31534	17714	8555	5265
Photoshop	25695	19547	4478	1670
PHP	14189	13439	594	156
PowerPoint	108758	46837	41646	20277
Sharepoint	12803	12657	102	44
Windows	34045	16383	10059	7603
Word	112999	39167	45279	28554

Para esse resultado eu criei os índices:

ALTER TABLE tblCurriculoConhecimento ADD INDEX (idCV,CampoConhecimento,NivelConhecimento);
ALTER TABLE tblCurriculoConhecimento ADD INDEX (CampoConhecimento,idCV);
ALTER TABLE tblCadastro_Curriculo_Incompleto ADD INDEX (Sexo,Ativo,Excluido,DtNascimento,DataUpdate,DataCadastro,Estado,Cidade,TipoDeficiencia,Instituicao,Curso,Dia,Ano,Nome,CPF,Email);

O cv.IdCV é um PRMARY KEY. Eu adicionei no INDEX campos a mais em relação a esta consulta, porque em certos momentos eles serão usados. O EXPLAIN tá me retornando isso:

1	SIMPLE	cv	index	PRIMARY	Sexo	1335 null		203349	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	cc	ref	    idCV	idCV	5	 cv.IdCV   4	Using where; Using index

Eu queria saber aonde estou errando. Porque minha index "Sexo" não está sendo usada corretamente. E como posso melhorar isso.

 

Por favor.

 

Obrigado,

Compartilhar este post


Link para o post
Compartilhar em outros sites

Valeu @Motta

 

Me ajuda em outra coisa, se puder, por favor.

 

Tenho o seguinte select:

 

SELECT cv.IdCV, cv.Nome, cv.CPF, cv.DtNascimento, cv.Curso, cv.Escolaridade, cv.Dia, cv.Ano, cv.TipoDeficiencia, cv.Resumo, cv.Informacoes, cv.ExperienciaProfissional, cv.Bairro, cv.Cidade, cv.Estado, IFNULL(Date(cv.DataUpdate),Date(cv.DataCadastro)) as UltimaAtualizacao 
FROM tblCadastro_Curriculo_Incompleto cv use index (busca_cv)
WHERE cv.Ativo = 'S' 
and cv.Excluido = 'N' 
and ((YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4) >= 16 or (datediff(YEAR(ifnull(Date(cv.DataUpdate),Date(cv.DataCadastro))),CURDATE()) + YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4)) >= 16) 
and (YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4) <= 97 or (datediff(YEAR(ifnull(Date(cv.DataUpdate),Date(cv.DataCadastro))),CURDATE()) + YEAR(CURDATE())-RIGHT(REPLACE(cv.DtNascimento, '/', ''),4)) <= 97)) 
group by cv.DataUpdate,cv.DataCadastro,cv.IdCV,cv.Nome,cv.CPF,cv.DtNascimento,cv.Curso,cv.Escolaridade,cv.Dia,cv.Ano,cv.TipoDeficiencia,Resumo,Informacoes,cv.ExperienciaProfissional,cv.Bairro,cv.Cidade,cv.Estado
LIMIT 0,10

 

quero de maneira performática, contar quantos registros tem sem o LIMIT 0,10

Compartilhar este post


Link para o post
Compartilhar em outros sites

O campo de data de nascimento é date ?

Deve ter uma forma mais simples de calcular a idade.

A demora é absurda ?

Alguns relatórios demoram , faz parte da coisa.

Se for o caso pense em índice por data de nascimento é faça o inverso calcule o intervalo de datas da busca.

Pelo que vi MySql não tem indice por função.

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Oi@Motta tudo bem? Obrigado por responder! O campo data de nascimento é varchar(20). Por isso a necessidade dos REPLACE e calcular a idade desse jeito. Mas o problema agora ñ é esse! Consegui reduzir de 40s para 5s a consulta. Mas eu preciso contar os  registros da consulta sem o LIMIT 0,10.

 

no PHP eu posso usar o rowCount() antes do LIMIT, mas depois vou ter que rodar a query de novo, e isso sobrecarrega o tempo de execução.

 

tá sendo um desafio. Pra você ter uma ideia, isso é um sistema de busca em AJAX que roda em 60s 10 scripts simultaneamente para carregar tudo. Mas tá f***! Sei que faz parte alguns relatórios demorarem, mas tô tentando performatizar o máximo.  Mesmo eu criando índices, não sei porque o explain retorna filesort. Porém, consegui melhorar bastante!

 

me dá uma sugestão de como contar os  registros da consulta sem o LIMIT 0,10, por favor.

 

Obrigado,

Compartilhar este post


Link para o post
Compartilhar em outros sites

Não conheço MySql profudamente.

Qual a razão do "limit" ?

Compartilhar este post


Link para o post
Compartilhar em outros sites

Aí realmente não conheço.

O filtro não pode ter a restrição aumentada ?

Em geral um usuário não busca tantos dados assim.

 

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

@Motta obrigado pela ajuda!

 

A solução que encontrei foi setar duas vezes a query usando as mesmas cláusulas where

 

Uma pra me trazer só o resultado do count

e outra pro relatório com index e o limit (0,10)

 

Porém, tive que excluir o group by do count pra ficar performático (só ñ sei se o resultado será o mesmo...rs).

 

Valeu!

 

Abraços,

Compartilhar este post


Link para o post
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar Agora

  • Conteúdo Similar

    • Por evandrogoncalves
      Eu preciso que o campo input tenha mais que uma linha, e tenha quebra de linha. Eu estou linkando em um código PHP onde mostrará o conteúdo atual no input e o usuário irá alterar o campo conforme queira, porém quando o texto é longo fica difícil achar o erro por aparecer tudo somente em uma linha. OBS : Tentei usar o TEXTAREA porém não consigo visualizar os dados, só escrever. 
    • Por artur0407
      Boa noite;
       
      Estou realizando testes com a biblioteca PDO para banco de dados com PHP;
      Logo de inicio estou realizando minha conexão com o banco utilizando um código simples nativo da biblioteca.
      No entanto, mesmo errando o código propositalmente e tendo setado todos os possíveis erros e tratando com o bloco "try catch" o php não me retorna erro de nenhuma forma.
       
      Abaixo ambos os códigos;
       
      Primeiro sem erros:
      <?php ini_set('display_errors',true); ini_set('display_startup_erros',1); error_reporting(E_ALL); $username = 'root'; $password = ''; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_CASE => PDO::CASE_NATURAL, PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING, PDO::ATTR_PERSISTENT => true ]; try { $conn = new PDO('mysql:host=localhost; dbname=test', $username, $password, $options); $conn->exec("set names utf8"); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } ?> Posteriormente com erro proposital (na string de conexão da classe PDO)
      <?php ini_set('display_errors',true); ini_set('display_startup_erros',1); error_reporting(E_ALL); $username = 'root'; $password = ''; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_CASE => PDO::CASE_NATURAL, PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING, PDO::ATTR_PERSISTENT => true ]; try { $conn = new PDO('mysql:hst=localhost; dbname=test', $username, $password, $options); $conn->exec("set names utf8"); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); } ?> Observem que no construtor do pdo, no lugar de host escrevi 'hst' e o php não retorna erros!
       
      Como sou novo aqui, me desculpem algum erro de formatação na mensagem, obrigado!
    • Por BryanSamuel
      Eu gostaria de importar dados de uma planinha que esteja em .xls para mysql, por exemplo, tem uma página html simples onde o usuário vai enviar a planilha, e com isso eu quero importar para uma base de dados, mas tem um porém, por exemplo, tem uma coluna com o número da matrícula de cada aluno, eu gostaria de fazer uma verificação, para que se já existisse um aluno com a matrícula igual, atualizasse somente determinadas colunas, como por exemplo, a coluna turma e a coluna turno, eu sei que parece pedir demais mas eu sou bem novato em php e mysql, se alguém puder ajudar agradeço desde já, abraço. 
      obs. de preferência, se tiver como importar direto de .xls eu agradeço, mas caso só de com .csv também serve
    • Por sobrinnho
      Olá pessoal, sou novo aqui e preciso da ajuda de vocês, fiz um site no dreamweaver CS6 e preciso conectar ele no BD do meu servidor online, no localhost consigo fazer as conexões, mas na hora de configurar pra o online só aparece erros.

    • Por paulaodaregulage
      Boa noite,
      Alguém saberia me dizer, como eu poderia descobrir o caminho de um arquivo no pc pela página web.
       
      assim:
      <form method="post" action="" enctype="multipart/form-data"> <input type="file" name="arquivo" /> <!--ele so mostra o arquivo que foi clicado e n o caminho completo...--> <input type="submit" value="Enviar" /> </form> e ao clicar no arquivo(arquivo.pdf ou video.mp4) desejado ,ele me daria  o caminho deste ,para que eu pudesse enviar  este caminho para meu banco de dados(mysql)
       
      exemplo : pasta1\pasta2\arquivo.pdf
       
      pq estou tendo dificuldades para enviar arquivos em pdf ou mp4 para uma pasta via web
      e desse jeito eu só teria q colocar o arquivo ou o vídeo na pasta manualmente e depois na pagina web eu só teria que clicar neste caminho para que a página encontrasse.
       
       
       

×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.