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 wilson c. antonio
      Eu recebo os dados via post, nome e cpf, mas minha consulta não retorna nenhum dado da tabela consultada.
      O que está errado?
      <?php include("../_conexao/funcoes.php"); $con=conectado(); #conferindo os dados recebidos via $_POST if(isset($_POST['btnLogin'])){ #addslashes(trim)remove as sobras de texto em branco dos campos $nome = addslashes(trim($_POST['nNome'])); $cpf = addslashes(trim($_POST['nCPF'])); } $sql=$con->prepare("SELECT * FROM bolsa WHERE bolsa.cpf = $cpf "); $sql->execute(); while($linha=$sql->fetch(PDO::FETCH_ASSOC)){ //paginação - somar quantidade de usuários $id_bolsa = $linha['id_bolsa']; $nome = $linha['nome']; $sobrenome = $linha['sobrenome']; $ddata = date('d/m/Y', strtotime($linha['dtprv'])); $nota = $linha['nota']; $acertos = $linha['acertos']; } ?> <input type="text" value="<?php echo $nota?>" readonly="true"/> <input type="text" value="<?php echo $cpf?>" readonly="true"/>  
    • Por Thiiiziiin
      Olá pessoal, como vão?
      Estou com o seguinte problema, tenho uma tabela em um banco de dados e estou pegando os valores para gerar gráficos, com a API Charts.js.
      Estou fazendo um gráfico dinâmico, em tempo real, porém, se eu insiro um dado no banco de dados, não consigo alterar no gráfico (é preciso dar refresh na página para alterar).
      Toda a conexão com o banco de dados está correta, pois se eu atualizo a página, o gráfico altera com os valores que defini.

      Eu fiz testes utilizando o math.random(), e funcionou, atualizava conforme os valores aleatórios gerados por essa função.
      Quando defino para ler do banco de dados, fica estático.
      Minha conclusão é que a variável de conexão do banco de dados não se altera em tempo real.

      Existe a possibilidade de atualizar a variável a cada segundo em PHP?
      Segue o código que estou fazendo:
       
      <?php include('conexao.php'); $select = $PDO->query("SELECT corrente FROM tbdados ORDER BY id DESC limit 1"); $select->execute(); $result = $select->fetch(); // retorno do banco de dados mysql_close($select); ?> // Abaixo é o javascript da API Charts.js modificada <script type="text/javascript"> var ctx = document.getElementById('myChart').getContext("2d"); var gradientStroke = ctx.createLinearGradient(0, 0, 0, 0); gradientStroke.addColorStop(0, '#FFC800'); var pointStroke = ctx.createLinearGradient(0, 0, 0, 0); pointStroke.addColorStop(0, '#3f3f3f'); var gradientFill = ctx.createLinearGradient(0, 250, 0, 50); gradientFill.addColorStop(0, "rgba(63, 63, 63, 0.7)"); gradientFill.addColorStop(1, "rgba(255, 182, 0, 1)"); var contador = 0; var corrente = <?php echo $result['corrente']; ?>; // variável que foi capturada do banco var segundos = 1000; <?php echo $rows; ?> var myChart = new Chart(ctx, { type: 'line', data: { labels: ["", "", "", "", "", "", "", "", "", "" ], datasets: [{ label: "Corrente (A)", borderColor: gradientStroke, pointBorderColor: gradientStroke, pointBackgroundColor: gradientStroke, pointHoverBackgroundColor: pointStroke, pointHoverBorderColor: gradientStroke, pointBorderWidth: 5, pointHoverRadius: 10, pointHoverBorderWidth: 1, pointHitRadius: 25, pointRadius: 3, fill: true, backgroundColor: gradientFill, borderWidth: 2, data: [, , , , , , , , , ,] }] }, options: { legend: { position: "bottom", labels: { fontColor: 'rgba(255,255,255,0.5)' } }, scales: { yAxes: [{ ticks: { fontColor: "rgba(255,255,255,0.5)", fontStyle: "bold", beginAtZero: true, maxTicksLimit: 10, padding: 20, }, gridLines: { drawTicks: false, display: false } }], xAxes: [{ gridLines: { zeroLineColor: "transparent" }, ticks: { padding: 20, fontColor: "rgba(255,255,255,0.5)", fontStyle: "bold" } }] } } }); function adddata(){ myChart.data.labels.push(contador + " segundos"); myChart.data.labels.splice(0, 1); myChart.data.datasets[0].data.splice(0, 1); console.log(myChart.data.datasets[0].data); myChart.data.datasets[0].data.push(corrente); myChart.update(); contador+=(segundos / 1000); } setInterval(function(){ adddata(); },segundos); var option = { showLines: true, scales: { yAxes: [{ display: true, ticks: { beginAtZero:true } }] } }; $(function() { setTime(); function setTime() { var date = new Date().getTime(); var string = "Corrente:<br/><span class='corrente_valor'>"+corrente+"</span><br/>Amperes"; setTimeout(setTime, segundos); $('#correnteCirculo').html(string); } }); </script>  
    • Por PedroTodorovski
      Quais a vantagens e as desvantagens de desenvolver um menu dinâmico (banco de dados) ao invés de um menu estático? Estou desenvolvendo um ecommerce e tenho essa dúvida.
    • Por brunobos21
      Olá, eu criei um formulario que salva os dados em um banco de dados mysql, e criei uma pagina de relatorio para mostrar todo mundo que se cadastra nesse banco, até ai tudo certo, só que eu tenho um campo "Status" que eu queria colocar um checkbox para o administrador poder marcar esse campo e mudar no mysql o valor do Status para 1 sempre que clicar no botão atualizar, e se o administrador desmarcar antes apertar o botão Atualizar, não mudar na tabela. Já pesquisei em tudo quer canto e não achei, se puderem me ajudar enviando algum modelo de código ou algum tópico aqui do forúm que ja fale sobre isso, agradeço. Aqui está meu código:
      <?php // definições de host, database, usuário e senha $host = "host"; $banco = "banco"; $user = "user"; $pass = "pass"; $tbl_name="tabela";   $nome = $_POST['nome']; $numero = $_POST['numero']; $email = $_POST['email']; $cidade = $_POST['cidade']; $whatsapp = $_POST['whatsapp']; $data = $_POST['data']; $status = $_POST['status'];   // conecta ao banco de dados $con = mysql_pconnect($host, $user, $pass) or trigger_error(mysql_error(),E_USER_ERROR); // seleciona a base de dados em que vamos trabalhar mysql_select_db($banco, $con); // cria a instrução SQL que vai selecionar os dados $query = sprintf("SELECT * FROM Pesquisas"); // executa a query $dados = mysql_query($query, $con) or die(mysql_error()); // transforma os dados em um array $linha = mysql_fetch_assoc($dados); // calcula quantos dados retornaram $total = mysql_num_rows($dados); ?> <!DOCTYPE html> <html lang="en"> <head>     <title>Relatorio</title>     <meta charset="latin1_swedish_ci">     <meta name="viewport" content="width=device-width, initial-scale=1"> <!--===============================================================================================-->       <link rel="icon" type="image/png" href="images/icons/favicon2.ico"/> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/bootstrap/css/bootstrap.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="fonts/font-awesome-4.7.0/css/font-awesome.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/animate/animate.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/select2/select2.min.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="vendor/perfect-scrollbar/perfect-scrollbar.css"> <!--===============================================================================================-->     <link rel="stylesheet" type="text/css" href="css/util.css">     <link rel="stylesheet" type="text/css" href="css/main.css"> <!--===============================================================================================--> <script src="vendor/jquery/jquery-3.2.1.min.js"></script> <script type="text/javascript"> //--> </script> </head> <body> <form action=" " method="post">     <div class="limiter">         <div class="container-table100">             <div class="wrap-table100">                 <div class="table100">                     <table>                         <thead>                             <tr class="table100-head">                                 <th class="column1">Nome</th>                                 <th class="column2">Numero</th>                                 <th class="column3">Cidade</th>                                 <th class="column4">E-mail</th>                                 <th class="column5">Whatsapp</th>                                 <th class="column6">Tipo</th>                                 <th class="column6">Data</th>                                 <th class="column6">Status</th>                             </tr>                         </thead>                           <?php                         // se o número de resultados for maior que zero, mostra os dados                         if($total > 0) {                     // inicia o loop que vai mostrar todos os dados                     do {                         ?>                         <tbody>                                 <tr>                                     <td class="column1"><?=$linha['nome']?></td>                                     <td class="column2"><?=$linha['numero']?></td>                                     <td class="column3"><?=$linha['cidade']?></td>                                     <td class="column4"><?=$linha['email']?></td>                                     <td class="column5"><?=$linha['whatsapp']?></td>                                     <td class="column6"><?=$linha['tipo']?></td>                                     <td class="column6"><?=$linha['data']?></td>                                     <td class="column6">                                     <input type=checkbox name="banco[]" value="1"><?=$linha['status']?>                                     </td>                                 </tr>                                </tbody>                         <?php                     // finaliza o loop que vai mostrar os dados                     }while($linha = mysql_fetch_assoc($dados));                         // fim do if                         }                         ?>                     </table>                     <div class="container-contact2-form-btn">                                 <div class="wrap-contact2-form-btn">                                     <div class="contact3-form-bgbtn"></div>                                     <button class="contact2-form-btn" type="submit" value="Enviar" name="enviar">                                         Atualizar                                     </button>                                 </div>                             </div>                 </div>             </div>         </div>     </div> </form>   <!--===============================================================================================-->       <script src="vendor/jquery/jquery-3.2.1.min.js"></script> <!--===============================================================================================-->     <script src="vendor/bootstrap/js/popper.js"></script>     <script src="vendor/bootstrap/js/bootstrap.min.js"></script> <!--===============================================================================================-->     <script src="vendor/select2/select2.min.js"></script> <!--===============================================================================================-->     <script src="js/main.js"></script> </body> </html> <?php // tira o resultado da busca da memória mysql_free_result($dados); ?>
×

Informação importante

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