(Resolvido) Consulta MySQL

[Marcelo Alves]

Ae galera estou com o seguinte problema. Tenho em uma tabela do meu banco mais de 20mil registros, no meu codigo php eu faço a consulta nesta tabela e na mesma consulta eu acesso outro banco que esta no mesmo servidor para comparar um valor. Só que esta muito lentooo.... e chega até da timeout. Gostaria de saber se alguém pode dar uma sugestão para resolver este problema, ou se o MySQL começa e da pau com muitos registros.

Agradeço desde já!!!

[ESerra]

Movendo...

PHP » MySQL

[fercosmig]

posta sua consulta ae, fica mais fácil de entender sua dúvida.

[Marcelo Alves]

Código:

$sql = "Select rp.num_pedido,rp.cod_vendedor,cli.nome,rp.cod_cliente,tc.razao_social,tc.nome,

            rp.dt_emissao,rp.dt_entrega,rp.desconto,rp.total_bruto,rp.observacao

            From rec_pedidos rp

            Inner Join outro_banco.CLIENTE cli on rp.cod_vendedor = cli.nVendedor

            Inner Join rec_itens_pedido ric on rp.num_pedido = ric.num_pedido

            Left Join tabela_produtos tp on ric.cod_produto = tp.id_produto 

            Left Join  tabela_clientes tc on rp.cod_cliente = tc.id_cliente

            Where 1 = 1";

    $filtro = "";

        
    if(is_numeric($_REQUEST['codPedido'])){
        $filtro .= " And rp.num_pedido = ".$_REQUEST['codPedido'];
    }elseif($_REQUEST['codPedido'] != ""){
        echo utf8_decode("0");
        exit();
    }
           
    if($_REQUEST['codVendedor'] != ""){
        if(is_numeric($_REQUEST['codVendedor'])){        
            $filtro .= " And rp.cod_vendedor = ".$_REQUEST['codVendedor'];
        }else{
            $filtro .= " And cli.nome like '".$_REQUEST['codVendedor']."'";
        }
    }
    
    if($_REQUEST['dataInicial'] != ""){
        if(checkdate($dtIniMes,$dtIniDia,$dtIniAno)){
            $data = preg_replace("'^([0-9]{2})/([0-9]{2})/([0-9]{4})$'",'$3-$2-$1', $_REQUEST['dataInicial']);
        $filtro .= 
            " And concat(If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),7,4),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),7,4)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),4,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),4,2)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),1,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),1,2))
           ) >= '".$data."'";
        }else{
            echo utf8_encode("1");
            exit();
        }        
    }
    
    if($_REQUEST['dataFinal'] !=""){
        if(checkdate($dtFimMes,$dtFimDia,$dtFimAno)){
            $data = preg_replace("'^([0-9]{2})/([0-9]{2})/([0-9]{4})$'",'$3-$2-$1', $_REQUEST['dataFinal']);
        $filtro .= 
            " And concat(If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),7,4),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),7,4)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),4,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),4,2)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),1,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),1,2))
           ) <= '".$data."'";
        }else{
            echo utf8_encode("2");
            exit();
        }                
    }
    
    if($_REQUEST['nomeCliente'] !=""){
        if(is_numeric($_REQUEST['nomeCliente'])){
            $filtro .= " And rp.cod_cliente = ".$_REQUEST['nomeCliente'];
        }else{
            $filtro .= " And tc.razao_social like '".$_REQUEST['nomeCliente']."'";
        }
    }
    
    if($_REQUEST['nomeProduto'] !=""){        
        if(is_numeric($_REQUEST['nomeProduto'])){
            $filtro .= " And tp.id_produto = ".$_REQUEST['nomeProduto'];
        }else{
            $filtro .= " And tp.descricao like '".$_REQUEST['nomeProduto']."'";
        }
    }
    
    $filtro .=" And cli.codEmpresa = '".    $_SESSION['empresa']."'";
    
    $filtro .= " Group By rp.num_pedido,rp.dt_emissao";
    
    switch($_REQUEST['ordenacao']){    
        case '1':
            $filtro .= " order by rp.num_pedido";            
        break;
        case '2':
            $filtro .= " order by rp.cod_Vendedor";
        break;
        case '3':
            $filtro .= " order by cli.nome";
        break;
        case '4':
            $filtro .= " order by rp.cod_cliente";
        break;
        case '5':
            $filtro .= " order by e.razao_social";
        break;
        case '6':
            $filtro .= " order by rp.dt_emissao";
        break;
        case '7':        
            $filtro .= " order by rp.dt_entrega";
        break;
        default:
            $filtro .= " order by rp.dt_entrega";
        break;
    }
    
    if($_REQUEST['ord'] != ''){
        $filtro .=" desc";
    }
    
    $sql .= $filtro;

    $sql .= " Limit ".($_REQUEST['pagina']*1).",50";
                
    $limit = 1;
    $exec = mysql_db_query($_SESSION['bancoCliente'],$sql,$con);

[Denis Courcy]

Oi, 'Marcelo Alves'!

Você já estudou otimização de consultas em bancos de dados? Existem várias publicações sobre mysql que tratam deste assunto.

Dê uma olhada neste tópico Tutoriais e Dicas, principalmente no post do Beraldo.

Se houvesse problemas com a quantidade de registros do MySQL, sites como Google, por exemplo, já teriam deixado de trabalhar com ele.

Para a correção do seu código vamos usar o método dividir para conquistar.

Primeiro, olhe o seu select abaixo:

"Select rp.num_pedido,rp.cod_vendedor,cli.nome,rp.cod_cliente,tc.razao_social,tc.nome,
    rp.dt_emissao,rp.dt_entrega,rp.desconto,rp.total_bruto,rp.observacao
From rec_pedidos rp
Inner Join outro_banco.CLIENTE cli on rp.cod_vendedor = cli.nVendedor
Inner Join rec_itens_pedido ric on rp.num_pedido = ric.num_pedido
Left Join tabela_produtos tp on ric.cod_produto = tp.id_produto 
Left Join  tabela_clientes tc on rp.cod_cliente = tc.id_cliente
Where 1 = 1";

Vamos analisá-lo: 1 - Você utilizou vários joins Os atributos cli.nVendedor, ric.num_pedido, tp.id_produto e tc.id_cliente possuem índices? Os joins realizam pesquisas extensivas para encontrar o que você deseja e quando não se utiliza índices o tempo de pesquisa crescerá exponencialmente conforme cresce o tamanho da tabela. Exemplo se a tabela rp (estou usando os aliases que você colocou em suas tabelas) possuir 1000 registros e você buscar algo dentro dela, ela varrerá os 1000 registros para te fornecer a resposta (Esta condição de varrer toda a tabela é conhecida como TABLE SCAN). Mesmo se o que você quizer estiver no 1º registro somente. Acrescentando um join, por exemplo a tabela cli (imaginando que ela também possua 1000 registros e não tendo índices), sua pesquisa agora varrerá 1000 x 1000 = 1.000.000 (um milhão) de registros para encontrar o que você quer. Se você acrescenta outra tabela, por exemplo, ric (que também possua 1000 registros), sua pesquisa varrerá 1000 x 1000 x 1000 = 1.000.000.000 (um bilhão) de registros para te fornecer a resposta. E você ainda incluiu mais duas tabelas. Imagine o tempo gasto para retornar a resposta! 2 - Você colocou uma condição que não filtra nada Pelo que entendi esta condição inútil de 1 = 1 serve para que você possa usar o termo WHERE da cláusula mesmo se o usuário não escolher filtro algum. Existem outras formas de programação para controlar este problema. pense um pouco mais. 3 - Os filtros abaixo são campos indexados? rp.num_pedido, rp.cod_vendedor, dt_emissao, rp.cod_cliente, tp.id_produto, tc.razao_social, tp.descricao, cli.codEmpresa Dependendo da constância de uso de um atributo como filtro é melhor indexá-lo para que a consulta não seja convertida em TABLE SCAN. 4- Não cometa o sacrilégio do código abaixo.

$filtro .= " And concat(If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),7,4),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),7,4)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),4,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),4,2)),'-',
           If(length(subString_Index(dt_emissao,' ',1))=9,
           SubString(insert(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),3,1,'-0'),1,2),
           SubString(Substring_Index(Replace((dt_emissao),'/','-'),' ',1),1,2))
           ) >= '".$data."'";

O uso de funções no lado onde está o atributo a ser comparado, força o uso de TABLE SCAN mesmo se houver índice para este atributo. A razão é simples, é necessário comparar linha por linha transformando o atributo para que coincida com o seu resultado. O padrão de data do mysql é o mais organizado que existe “aaaa-mm-dd”. Modifique sua variável para que fique igual a o atributo data que você quer comparar. 5 – Você criou índices para os atributos nas cláusulas GROUP BY e ORDER BY? Os índices nestas cláusulas aceleram o tempo de resposta para agrupamento e ordenação

$filtro .= " Group By rp.num_pedido,rp.dt_emissao";
        switch($_REQUEST['ordenacao']){    
        case '1':
            $filtro .= " order by rp.num_pedido";            
        break;
        case '2':
            $filtro .= " order by rp.cod_Vendedor";
        break;
        case '3':
            $filtro .= " order by cli.nome";
        break;
        case '4':
            $filtro .= " order by rp.cod_cliente";
        break;
        case '5':
            $filtro .= " order by e.razao_social";
        break;
        case '6':
            $filtro .= " order by rp.dt_emissao";
        break;
        case '7':        
            $filtro .= " order by rp.dt_entrega";
        break;
        default:
            $filtro .= " order by rp.dt_entrega";
        break;
    }
    
    if($_REQUEST['ord'] != ''){
        $filtro .=" desc";
    }
    
    $sql .= $filtro;

    $sql .= " Limit ".($_REQUEST['pagina']*1).",50";
                
    $limit = 1;
    $exec = mysql_db_query($_SESSION['bancoCliente'],$sql,$con);

[Marcelo Alves]

Denis muito obrigado pela ajuda cara, resolveu meu problema!!!! Abraços e sucesso!