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 Perroni91
      Olá pessoal.
      Estou com uma dúvida, mas como sou leiga no mysql, não estou conseguindo resolver.
       
      Tenho duas tabelas no meu banco de dados:
       
      Posts
      id
      titulo_principal
      categoria_id
      imagem
       
      Categorias
      id
      categoria
       
      ---

      Já cadastrei as categorias 'HTML' e 'CSS', e cada um contêm 1 post de cada.

      O que eu gostaria era que: A cada vez que criar um post em alguma dessas categorias, mostrar a quantidade separadamente.

      Ficaria assim:
      HTML               3
      CSS                   1


      Alguém poderia me ajudar, por favor?
      Estou quebrando a cabeça tentando fazer isso e não consegui.
      Obrigada!
    • Por danart5
      Bom dia! Estou fazendo um sistema de chamados, e na pagina onde o cliente envia para mim os dados do seu chamado, gostaria de trazer um aviso onde informasse o ID do chamado do mesmo.
      No banco de dados tem o campo id_ocorrencia, é auto incremente e assim que o usuário cadastra a ocorrência é cadastrado um id_ocorrencia.
      Segue o código :
       
       
      <div class="cadastro-form">         <?php             $nome_solicitante = $_POST['nome_solicitante'];             $ccusto = $_POST['ccusto'];             $equip = $_POST['equip'];             $tp_problema = $_POST['tipo_problema'];             $obs_informada = $_POST['obs_informada'];             $data_cadastro = date('Y-m-d');             $hora_cadastro = date('H:i:s');             //Conexão com o MySQL             $conn = mysql_connect('localhost','root','123456');             if (!$conn) {                 die('Could not connect: ' . mysql_error($conn));             }             mysql_select_db("osafti", $conn);             mysql_set_charset("utf8", $conn);             $sqli = mysql_query("INSERT INTO cadOcor (nome_solicitante, ccusto, equip, data_cadastro, hora_cadastro, tipo_problema, obs_informada, status) VALUES ('$nome_solicitante', '$ccusto', '$equip', '$data_cadastro', '$hora_cadastro', '$tp_problema', '$obs_informada', '0')", $conn) or die (mysql_error());             if ($sqli) {             ?>                            --------- NESSA REGIÃO É ONDE GOSTARIA DE TRAZER O ID DO CADASTRO FEITO A CIMA, É POSSÍVEL ?  ----                 <div class="alert alert-success" role="alert">                     Ocorrência cadastrada com sucesso!                  </div> <?php } else { ?> <div class="alert alert-danger" role="alert"> Erro ao cadastrar ocorrência. Tente novamente. Caso o erro persista entre em contato com o CPD. </div> <?php } mysql_close($conn); ?> </div>
    • Por gersonab
      há tempos utilizo para cadastrar data no BD a instrução :
      implode('-',array_reverse(explode('/',$data)))
      com isso no input posso colocar 09/02/2019 e no banco de dados grava 2019-02-09 , até ai sem problemas, porém ao atualizar um sistema para pdo, estou com problema no insert dinâmico, onde posso fazer 1 ou mais inserts, da forma abaixo cadastro quantos eu quiser :
      $sql = "INSERT INTO cantos ( id_t, id_c, data, ca, cant, po, des) VALUES (:id_t, :id_c, :data, :ca, :cant, :po, :des)";              $stmt = $pdo->prepare($sql);             foreach($_POST['idp'] as $indice => $valor) {             $stmt->bindParam(':id_t', $_POST['id_tor'][$indice], PDO::PARAM_STR);             $stmt->bindParam(':id_c', $_POST['idp'][$indice], PDO::PARAM_STR);             $stmt->bindParam(':data', $_POST['data'][$indice], PDO::PARAM_STR);             $stmt->bindParam(':ca', $_POST["ca"][$indice], PDO::PARAM_STR);             $stmt->bindParam(':cant', $_POST['cant'][$indice], PDO::PARAM_STR);             $stmt->bindParam(':po', $_POST['po'][$indice], PDO::PARAM_STR);             $stmt->bindParam(':des', $_POST['des'][$indice], PDO::PARAM_STR);                              if ($stmt->execute()){                     echo "Cadastrado com sucesso.";             }
      no entento a data vai no formato do input 09/02/2019.
      caso utilize :
      $stmt->bindParam(':data', implode('-',array_reverse(explode('/',$_POST['data'][$indice]))), PDO::PARAM_STR);
      tenho o erro Strict standards: "Only variables should be passed by reference in "que significa "Apenas as variáveis podem ser passadas por referência"
      qual a melhor forma de se fazer neste caso ?
    • Por lendadomato
      Pessoal,

      tenho alguns bancos de dados mysql e em servidores diferentes, o problema é que de um tempo
      pra cá ao inserir dados que tenham sinal de + nos campo tipo varchar, ele simplesmente ignora o sinal.
      Tenho aplicativos que os administradores cadastram combo de pizzas no exemplo:
      - Combo pizza Grande + Refri + borda catupiry

      Agora parece que alterou algo no banco que não é possível cadastrar mais com o sinal de + ficando assim:
      - Combo pizza Grande    Refri    borda catupiry
       
      O sistema substitui o sinal de + por dois espaços.
      Alguém sabe o que está acontecendo?
      Não fiz nenhum alteração no sistema que há muito tempo funcionava perfeitamente.
    • Por AllysonSouza
      Boa noite pessoal, passei dia todo batendo a cabeca e ainda nao descobri como fazer para o autocomplete listar o id em outro input, quero que depois do autocomplete enviar, ele envie apenas o ID da RUA, eu consigo puxar o id junto com o nome da rua no mesmo campo imput, porem queria que o id fosse para outro campo input para eu enviar para o banco de dados, bom segue o codigo:
      index.php
      <?php session_start(); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>jQuery Autocomplete Plugin</title> <script type="text/javascript" src="js/jquery-1.4.2.js"></script> <script type='text/javascript' src="js/jquery.autocomplete.js"></script> <link rel="stylesheet" type="text/css" href="js/jquery.autocomplete.css" /> <script type="text/javascript"> $().ready(function() { $("#course").autocomplete("autoComplete.php", { width: 260, matchContains: true, //mustMatch: true, //minChars: 0, //multiple: true, //highlight: false, //multipleSeparator: ",", selectFirst: false }); }); </script> </head> <body> <div id="content" > <form autocomplete="off"> <p> Digite a rua : <input type="text" name="course" id="course" /> </p> </form> </div> </body> </html> autocomplete.php
      <?php include "conexao.php"; $q = strtolower($_GET["q"]); if (!$q) return; $sql = "select DISTINCT id, nome from ruas where nome LIKE '%$q%'"; $rsd = mysqli_query($con, $sql); while($rs = mysqli_fetch_array($rsd)) { $cid = $rs['id']; $cnome = $rs['nome']; echo "$cid $cnome\n "; } ?>  
×

Informação importante

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