Ir para conteúdo

POWERED BY:

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

ndias

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

Por , em MySQL

Recommended Posts

ndias    2

ndias
Postado

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

ndias    2

ndias
Postado

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

Motta    645

Motta
Postado

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

ndias    2

ndias
Postado

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

Motta    645

Motta
Postado

Não conheço MySql profudamente.

Qual a razão do "limit" ?

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

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

ndias    2

ndias
Postado

@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
Ir para a lista de tópicos MySQL

  • Conteúdo Similar

    • ILR master
      Consulta sem duplicação
      Por ILR master
      Tudo bem pessoal?
       
      No código abaixo, estou fazendo uma consulta nas tabelas, banners e banners_referencia
      Meu objetivo é trazer resultados com valores iguais ao nome da cidade declarada na $cidade ou resultados com a referencia Total.
      O problema é que está trazendo todos os resultados. Tenho 10 linhas, 1 com o nome da cidade e duas com o valor Total, então o resultado teria que ser de apenas 3 linhas, mas mostra tudo.
       
      $banner = "SELECT A.*, B.* FROM banners A, banners_referencia B WHERE B.cod_referencia = A.cod_referencia AND A.cidade = '$cidade' OR B.referencia = 'Total' ORDER BY RAND()";
      $banner = mysqli_query($conexao, $banner) or die ("Banner não encontrado");
      while($busca= mysqli_fetch_array($banner)){
          print $busca['cidade'].'<br>';
      };
       
      Alguém consegue me ajudar?
    • luiz monteiro
      php + mysql - where do select em campo varchar com dados numéricos com zeros a direita
      Por luiz monteiro
      Olá, tudo bem?
       
      Estou melhorando meu conhecimento em php e mysql e, me deparei com o seguinte. A tabela da base de dados tem um campo do tipo varchar(8) o qual armazena números. Eu não posso alterar o tipo desse campo. O que preciso é fazer um select para retornar o números que contenham zeros a direita ou a esquerda.
      O que tentei até agora
       
      Ex1
      $busca = $conexao->prepare("select campo form tabela where (campo = :campo) ");
      $busca->bindParam('campo', $_REQUEST['campo_form']);
       
      Se a direita da string $_REQUEST['campo_form'] termina ou inicia com zero ou zeros, a busca retorna vazio.
      Inseri dados numéricos, da seguinte maneira para testar: 01234567;  12345670: 12345678: 12340000... entre outros nessa coluna. Todos os valores que não terminam ou não iniciam com zero ou zeros, o select funciona.
       
       
      Ex2
      $busca = $conexao->prepare("select campo form tabela where (campo = 0340000) ");
      Esse número está cadastrado, mas não retorna.
       
      Ex3
      $busca = $conexao->prepare("select campo form tabela where (campo = '02340001' ) ");
      Esse número está cadastrado, mas não retorna.
       
       
      Ex4
      $busca = $conexao->prepare("select campo form tabela where (campo like 2340000) ");
      Esse número está cadastrado, mas não retorna.
       
      Ex5
      $busca = $conexao->prepare("select campo form tabela where (campo like '12340000') ");
      Esse número está cadastrado, mas não retorna.
       
      Ex6
      $busca = $conexao->prepare("select campo form tabela where (campo like '"12340000"' ) ");
      Esse número está cadastrado, mas não retorna.
       
       
      Ex7
      $busca = $conexao->prepare("select campo form tabela where (campo like :campo) ");
      $busca->bindParam('campo', $_REQUEST['campo_form'])
      Não retorna dados.
       
      O  $_REQUEST['campo_form'] é envio via AJAX de um formulário. 
      Usei o gettype para verificar o post, e ele retorna string.
      Fiz uma busca com número 12345678 para verificar o que o select retorna, e também retrona como string.
       
      Esse tipo de varchar foi usado porque os números que serão gravados nesse campo,  terão zeros a direita ou na esquerda. Os tipos number do mysql não gravam zeros, então estou usando esse. O problema é a busca.
      Agradeço desde já.
       
       
    • ILR master
      Conexão mysql com outro domínio
      Por ILR master
      Fala galera, tudo certo?
       
      Seguinte: No servidor A estou tentando fazer uma consulta com o servidor B, mas está dando erro.
      Estou usando o mesmo código de conexão do servidor B que funciona perfeitamente, mas no servidor A, dá erro.
      Segue código:
       
      $host = 'servidor B';
      $user = 'user';
      $pass = '********';
      $db   = 'banco';
       
      // conexão e seleção do banco de dados
      $conexao = mysqlI_connect($host, $user, $pass, $db);
      mysqlI_set_charset($conexao,"utf8");
      //print "Conexão rodando e OK!"; 
      //mysqlI_close($conexao);
       
      Alguém pode me ajudar?
    • douglas79
      Problemas com a última versão do MYSQL
      Por douglas79
      Bom dia,

      Há alguns dias que venho instalar o apache, o php, mysql e o phpmyadmin manualmente e sem obter sucesso. Até consegui rodar o php, porém, quando vou baixar a úitima versão do MYSQL, não tem todos os pacotes nele instalados, inclusive no completo, só encontro o Router.
      Alguém pode me dizer o porquê que isso está ocorrendo?
      Desde já agradeço a ajuda de vocês, que será bem vinda!
      No aguardo!

      Uso a versão 8.3.9 do PHP
      Meu SO é o Windows 10 32 bits
    • landerbadi
      Código pho não executa em banco de dados grande
      Por landerbadi
      Tenho uma tabela chamada "item" com os seguintes campos: id, name, active. Nela tem cadastrado vários itens. No campo "active" eu coloco a letra "S" para informar que este item está ativo no sistema. Por exemplo: 1, casa, S 2, mesa, S 3, cama, S 4, moto S 5, rádio O quinto registro "radio" não está ativo no sistema pois não tem um "S" no campo active. E outra tabela chamada "product" com os seguintes campos (id, name) com os seguintes registros: 1, Produto A 2, Produto B 3, Produto C E uma terceira tabela chamada "product_item" com os seguintes campos (productID, itemID). No campo productID eu coloco o id de um produto da tabela "product" e no campo "itemID" eu coloco o id do produto da tabela "item". Exemplo: 1, 1 1, 3 1, 4 2, 3 2, 4 Sendo assim o produto A da tabela 'product" comtem os itens casa, cama e moto. Eu preciso fazer uma busca da seguinte maneira:  Eu escolho um registro da tabela "item", por exemplo "casa". Preciso fazer com que o php me liste todos os registros da tabela "product" que contenham a palavra "casa" e que os demais itens estejam ativos no siste. Ou seja, que contenham um "S" no campo "active"  Eu consegui fazer isso da seguinte maneira: SELECT P.id, P.name, GROUP_CONCAT(I.name ORDER BY I.name) AS items FROM product P JOIN product_item PI ON P.id = PI.productID JOIN item I ON I.id = PI.itemID AND I.active = 'S' WHERE P.id NOT IN ( SELECT PI.productID FROM product_item PI JOIN item I ON I.id = PI.itemID WHERE I.active IS NULL ) AND P.id IN ( SELECT PI.productID FROM product_item PI JOIN item I ON I.id = PI.itemID WHERE I.name = 'mesa' ) GROUP BY P.id, P.name; O problema que eu estou tendo é o seguinte:
      Quando eu jogo este código para o banco de dados onde eu já tenho os registros cadastrado o php fica lendo uma eternidade e não lista os produtos.
       
      Usando código no banco de dados que eu fiz para testes ele funciona perfeitamente pois nele tem poucos registros.
       
      No banco de dados principal a tabela "item" tem 11.196 registros. A tabela "product" tem 88.214 registros e a tabela "product_item" tem 518.378 registros. 
       
      Eu acredito que, devido o banco de dados ser muito grande, ele não consegue listar.
       
      Alguém sabe de algum meio de resolver isso?
       
       
×

Informação importante

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

  Aceito