Php page query takes 15 minutes and the same query on phpmyadmin takes 3 seconds

Question

Php page query takes 15 minutes and the same query on phpmyadmin takes 3 seconds,no sense GIANT DIFFRENCE I'am using a server in the intranet, the php file is in the same server as mysql, and the phpmyadmin is also in this same server. The tables involved are below 20k rows, but the final result is only 144 rows. Here is the query:

SELECT a.cod_empresa, a.dat_alt_sit, a.cod_cliente, a.num_pedido, 
        b.qtd_pecas_solic, b.qtd_pecas_cancel, b.qtd_pecas_atend, b.cod_item, b.num_pedido, 
        c.nom_cliente, 
        d.qtd_liberada, d.qtd_reservada 
FROM lx_pedidos a, 
     lx_ped_itens b, 
     lx_clientes c, 
     lx_estoque d 
WHERE 
    a.num_pedido = b.num_pedido 
AND a.cod_empresa = b.cod_empresa 
AND c.cod_cliente = a.cod_cliente 
AND b.cod_item = d.cod_item 
AND b.cod_empresa = d.cod_empresa 
AND a.ies_sit_pedido = 'N' 
AND a.cod_nat_oper <> 9001 
AND d.qtd_liberada > 0 
AND (b.qtd_pecas_solic - b.qtd_pecas_cancel - b.qtd_pecas_atend) > 0 
AND (a.cod_empresa = 2 or a.cod_empresa = 3 or a.cod_empresa = 5) 
AND a.dat_alt_sit > '2018-07-01' 
AND a.dat_alt_sit < '2018-07-31'                                      
ORDER BY b.cod_item

php code:

    echo 'point 2 '.hojeDataHoraHuman();
    $result = mysqli_query($conn, "QUERY^^^");

    echo 'point 3 '.hojeDataHoraHuman();


    $cont1 = 0;
    $totGeral = 0;
    while($row = mysqli_fetch_assoc($result)){
        $cont1++;
(...)

CREATE TABLEs

CREATE TABLE lx_pedidos ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_empresa         int(2) NULL,
    num_pedido          int(6) NULL,
    cod_cliente         bigint(15) NULL,
    pct_comissao        varchar(5) NULL,
    cod_nat_oper        int(4) NULL,
    cod_transpor        varchar(15) NULL,
    ies_finalidade      int(1) NULL,
    ies_frete           int(1) NULL,
    ies_preco           varchar(1) NULL,
    cod_cnd_pgto        int(3) NULL,
    ies_embal_padrao    int(1) NULL,
    ies_tip_entrega     int(1) NULL,
    ies_aceite          varchar(1) NULL,
    ies_sit_pedido      varchar(1) NULL,
    dat_pedido          date NULL,
    num_pedido_cli      varchar(25) NULL,
    num_list_preco      int(4) NULL,
    cod_repres          int(4) NULL,
    dat_alt_sit         date NULL,
    dat_cancel          date NULL,
    cod_motivo_can      varchar(4) NULL,
    dat_ult_fatur       date NULL,
    cod_moeda           int(1) NULL,
    ies_comissao        varchar(1) NULL,
    cod_tip_carteira    int(2) NULL,
    data_ult_sync       datetime NOT NULL,
    id_sync             int(11) NOT NULL,
    info                varchar(255) NOT NULL,
    PRIMARY KEY(id)
)

-

CREATE TABLE lx_clientes ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_cliente         varchar(15) NULL,
    cod_class           varchar(1) NULL,
    nom_cliente         varchar(36) NULL,
    dat_cadastro        varchar(10) NULL,
    end_cliente         varchar(36) NULL,
    den_bairro          varchar(23) NULL,
    cod_cidade          varchar(9) NULL,
    cod_cep             varchar(9) NULL,
    num_caixa_postal    varchar(15) NULL,
    num_telefone        varchar(15) NULL,
    num_suframa         varchar(9) NULL,
    cod_tip_cli         varchar(2) NULL,
    den_marca           varchar(15) NULL,
    nom_reduzido        varchar(29) NULL,
    den_frete_posto     varchar(19) NULL,
    num_cgc_cpf         varchar(19) NULL,
    ins_estadual        varchar(16) NULL,
    cod_portador        varchar(3) NULL,
    ies_tip_portador    varchar(15) NULL,
    cod_consig          varchar(15) NULL,
    ies_cli_forn        varchar(1) NULL,
    ies_zona_franca     varchar(1) NULL,
    ies_situacao        varchar(1) NULL,
    cod_rota            int(5) NULL,
    cod_praca           varchar(10) NULL,
    dat_atualiz         varchar(20) NULL,
    nom_contato         varchar(20) NULL,
    dat_fundacao        varchar(9) NULL,
    cod_local           int(1) NULL,
    PRIMARY KEY(id)
)

-

CREATE TABLE lx_estoque ( 
id              int(11) AUTO_INCREMENT NOT NULL,
cod_empresa     int(2) NULL,
cod_item        varchar(15) NULL,
qtd_liberada    int(9) NULL,
qtd_reservada   int(3) NULL,
dat_ult_entrada date NULL,
dat_ult_saida   date NULL,
PRIMARY KEY(id)
)

-

CREATE TABLE lx_ped_itens ( 
    id                  int(11) AUTO_INCREMENT NOT NULL,
    cod_empresa         int(2) NULL,
    num_pedido          int(6) NULL,
    num_sequencia       int(2) NULL,
    cod_item            varchar(30) NULL,
    pct_desc_adic       decimal(4,2) NULL,
    pre_unit            decimal(12,3) NULL,
    qtd_pecas_solic     int(4) NULL,
    qtd_pecas_atend     int(3) NULL,
    qtd_pecas_cancel    int(4) NULL,
    qtd_pecas_reserv    int(1) NULL,
    prz_entrega         date NULL,
    qtd_pecas_romaneio  int(1) NULL,
    pct_desc_bruto      int(2) NULL,
    data_ult_sync       datetime NOT NULL,
    id_sync             int(2) NOT NULL,
    info                varchar(255) NOT NULL,
    PRIMARY KEY(id)
)

result in phpmyadmin: results

mysqli statement: php code query

Add: I made a query now with join and it stays running forever in phpmyadmin and in my php page, but this query works in informix DB where I have this same tables: join query (why??)

The time of 15 minutes is between the point 2 and point 3. Please, any suggestion is usefull

Answer 1

Add few indexes to your schema:

ALTER TABLE lx_pedidos ADD INDEX dat_alt_sit_idx (dat_alt_sit, cod_empresa, cod_nat_oper);
ALTER TABLE lx_pedidos ADD INDEX num_pedido_idx (num_pedido);
ALTER TABLE lx_pedidos ADD INDEX cod_cliente_idx (cod_cliente);
ALTER TABLE lx_clientes ADD INDEX cod_cliente_idx (cod_cliente);
ALTER TABLE lx_ped_itens ADD INDEX num_pedido_idx (num_pedido);
ALTER TABLE lx_ped_itens ADD INDEX cod_item_idx (cod_item, cod_empresa);
ALTER TABLE lx_estoque ADD INDEX cod_item_idx (cod_item, cod_empresa);

And change your query to use JOINs:

SELECT a.cod_empresa, a.dat_alt_sit, a.cod_cliente, a.num_pedido, 
        b.qtd_pecas_solic, b.qtd_pecas_cancel, b.qtd_pecas_atend, b.cod_item, b.num_pedido, 
        c.nom_cliente, 
        d.qtd_liberada, d.qtd_reservada 
FROM lx_pedidos a
JOIN lx_ped_itens b
ON a.num_pedido = b.num_pedido
    AND a.cod_empresa = b.cod_empresa
    AND (b.qtd_pecas_solic - b.qtd_pecas_cancel - b.qtd_pecas_atend) > 0
JOIN lx_clientes c
ON c.cod_cliente = a.cod_cliente
JOIN lx_estoque d
ON b.cod_item = d.cod_item 
    AND b.cod_empresa = d.cod_empresa
    AND d.qtd_liberada > 0
WHERE a.ies_sit_pedido = 'N' 
AND a.cod_nat_oper <> 9001 
AND (a.cod_empresa = 2 or a.cod_empresa = 3 or a.cod_empresa = 5) 
AND a.dat_alt_sit > '2018-07-01' 
AND a.dat_alt_sit < '2018-07-31'  

With this DB changes applied: Query took 0.2310 seconds.

NOTE You have a big issue. Table lx_pedidos a has column cod_cliente bigint(15) NULL, but table lx_clientes c has column cod_cliente varchar(15) NULL, that is why JOIN lx_clientes c is pretty slow. That will be way more faster if you switch lx_clientes to use same type bigint(15)

ALTER TABLE lx_clientes MODIFY COLUMN cod_cliente bigint(15);

This query fails on your dataset. Check your record select * from lx_clientes where id = 9290 . It has - symbol. When you change that value - ALTER TABLE should work.

After this modification: Query took 0.0041 seconds ;-)