Improve the performance of a query

Question

Improve the performance of a query

Navigation

#1 by (1 votes)

6

How could I improve this query, which returns me the top 10 sales to assemble a chart, so that it processes faster?


set @startDate :='2015-01-03';
set @endDate :='2015-05-31';
set @dst_id:=1;


SELECT
  SUM(vnd_quantidade) as quantidade,
  (SUM(vnd_faturamento) / (SELECT
     SUM(vnd_faturamento) as valor from
                  venda where vnd_dt
                      between @startDate and @endDate 
                     and dst_id=@dst_id) * 100) as porcentagem_tooltip,
     SUM(vnd_faturamento) as faturamento,
    (SUM(vnd_faturamento) / (SELECT
         SUM(valores.valor) as Total from
         ((SELECT sum(vnd_quantidade) as qtd,
                  sum(vnd_faturamento) as valor,
                  prd_id from venda where vnd_dt 
                  between @startDate and @endDate 
                  and dst_id=@dst_id group by prd_id order by qtd desc
           limit 10) valores)) * 100) as total_grafico,
venda.prd_id, produto.prd_nome as Produto
from venda 
INNER JOIN produto ON(produto.prd_id=venda.prd_id)
where vnd_dt between @startDate and @endDate and dst_id=@dst_id
group by prd_id order by porcentagem_tooltip desc,
quantidade desc,
faturamento desc 
limit 10

I would like to avoid so many SUBQUERYS.

My table is displaying something like this:


+-----------------------------------------------------------------------+
|quantidade|porcentagem|tooltip_faturamento|total_grafico|prd_id|Produto|
+-----------------------------------------------------------------------+
|  1499    | 11,754075 |     686091,43     |  21,208158  | 162  | 27285 |
|  2630    | 9,838371  |     574270,77     |  17,751606  | 178  | 27395 |
|  1183    | 7,123907  |     415826,15     |  12,853836  | 174  | 27392 |
|  2053    | 7,097845  |     414304,88     |  12,806811  | 179  | 27396 |
|  1677    | 6,852019  |     399955,87     |  12,363261  | 207  | 27915 |
|  8526    | 5,728544  |     334378,08     |  10,336149  | 14   | 16520 |
|  5261    | 5,707067  |     333124,42     |  10,297396  | 4    | 15970 |
|  764     | 4,297695  |     250858,68     |  7,754434   | 175  | 27393 |
|  898     | 3,415646  |     199373        |  6,162931   | 208  | 27916 |
|  447     | 3,342078  |     195078,84     |  6,030192   | 163  | 27286 |
+-----------------------------------------------------------------------+

With EXPLAIN, I get the following result:


+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  id  |      select_type     |  table    |  type | possible_keys |  key  | key_len |       ref         |  rows  |                    Extra                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1   |       PRIMARY        |  venda    | range | prd_id,vnd_dt | vnd_dt|    3    |                   |  16297 | Using where; Using temporary; Using filesort |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1   |       PRIMARY        | produto   | eq_ref| PRIMARY,prd_id|PRIMARY|    2    |DB123.venda.prd_id |    1   |                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  3   | UNCACHEABLE SUBQUERY | <derived4>|  ALL  |               |       |         |                   |   10   |                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  4   |        DERIVED       |  venda    | range |    vnd_dt     |vnd_dt |    3    |                   |  16297 | Using where; Using temporary; Using filesort |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  2   | UNCACHEABLE SUBQUERY |  venda    |  ALL  |    vnd_dt     |       |         |                   |  59245 | Using where                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+

And these are the tables:


CREATE TABLE 'venda' (
  'vnd_id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'rvd_id' mediumint(7) unsigned NOT NULL DEFAULT '0',
  'clt_id' int(11) DEFAULT NULL,
  'dst_id' tinyint(2) unsigned NOT NULL DEFAULT '0',
  'fll_id' tinyint(2) unsigned DEFAULT '0',
  'reg_id' tinyint(4) NOT NULL DEFAULT '0',
  'prd_id' smallint(3) unsigned NOT NULL DEFAULT '0',
  'usr_id' mediumint(7) unsigned NOT NULL DEFAULT '0' COMMENT 'Vendedor',
  'ram_id' int(11) NOT NULL,
  'uni_id' int(11) NOT NULL,
  'xls_id' mediumint(7) NOT NULL,
  'vnd_dt' date NOT NULL DEFAULT '0000-00-00' COMMENT 'Data da Venda',
  'vnd_quarter' date NOT NULL DEFAULT '0000-00-00',
  'vnd_nf' varchar(20) COLLATE latin1_general_ci NOT NULL,
  'vnd_quantidade' int(10) NOT NULL DEFAULT '0',
  'vnd_erro' tinyint(1) unsigned NOT NULL DEFAULT '0',
  'vnd_pts' enum('S','N') COLLATE latin1_general_ci NOT NULL DEFAULT 'N',
  'vnd_pts_combinacao' enum('S','N') COLLATE latin1_general_ci NOT NULL DEFAULT 'N',
  'vnd_pts_frequencia' enum('S','N') COLLATE latin1_general_ci NOT NULL DEFAULT 'N',
  'vnd_obs' varchar(120) COLLATE latin1_general_ci DEFAULT NULL,
  'vnd_produto_obs' varchar(155) COLLATE latin1_general_ci DEFAULT NULL,
  'vnd_faturamento' double(10,2) DEFAULT NULL,
  'vnd_unitario' double(10,2) DEFAULT '0.00',
  'vnd_clt_faturamento' double(10,2) DEFAULT '0.00',
  'vnd_faturamento_net' double(10,2) DEFAULT '0.00',
  'vnd_clt_unitario' double DEFAULT '0',
  'vnd_dt_cadastro' date NOT NULL,
  PRIMARY KEY ('vnd_id','rvd_id','dst_id','prd_id','usr_id'),
  KEY 'rvd_id' ('rvd_id'),
  KEY 'prd_id' ('prd_id'),
  KEY 'vnd_dt' ('vnd_dt'),
  KEY 'vnd_quantidade' ('vnd_quantidade'),
  KEY 'vnd_pts' ('vnd_pts'),
  KEY 'vnd_erro' ('vnd_erro'),
  KEY 'ram_id' ('ram_id'),
  KEY 'uni_id' ('uni_id'),
  KEY 'xls_id' ('xls_id')
) ENGINE=MyISAM AUTO_INCREMENT=59246 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;



CREATE TABLE 'produto' (
  'prd_id' smallint(3) unsigned NOT NULL AUTO_INCREMENT,
  'tip_id' smallint(5) unsigned DEFAULT NULL,
  'lob_id' smallint(5) unsigned DEFAULT NULL,
  'sgm_id' smallint(5) unsigned DEFAULT NULL,
  'lnh_id' smallint(5) unsigned DEFAULT NULL,
  'fml_id' smallint(5) unsigned DEFAULT NULL,
  'prd_nome' varchar(40) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Part Number',
  'prd_categoria' varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  'prd_ativo' enum('S','N') COLLATE latin1_general_ci NOT NULL DEFAULT 'S',
  'prd_erro' enum('S','N') COLLATE latin1_general_ci DEFAULT 'N',
  PRIMARY KEY ('prd_id'),
  KEY 'prd_id' ('prd_id'),
  KEY 'produto_FKIndex1' ('tip_id'),
  KEY 'produto_FKIndex2' ('lob_id'),
  KEY 'produto_FKIndex3' ('sgm_id'),
  KEY 'produto_FKIndex4' ('lnh_id'),
  KEY 'produto_FKIndex5' ('fml_id')
) ENGINE=MyISAM AUTO_INCREMENT=293 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

mysql sql mysql-workbench

asked by anonymous 29.06.2015 / 14:25

1 answer

HTTP GET using localhost Write the result of a variable in the mysql database using python passed as parameter

score 1 · Accepted Answer

If I understand your SQL correctly, SubQuerys are not dependent on the main Query. This way you can use "pre-defined" variables with the values of SubQuerys, thus guaranteeing a single execution of SubQuerys.

Edited

You can also improve performance by doing the join only at the end after the calculations.

set @startDate :='2015-01-03';
set @endDate :='2015-05-31';
set @dst_id:=1;

SELECT
    @total1 := SUM(vnd_faturamento)
from venda 
    where 
        vnd_dt between @startDate and @endDate 
        and dst_id=@dst_id;

SELECT
    @total2 := SUM(valores.valor)
from (
    SELECT sum(vnd_quantidade) as qtd,
        sum(vnd_faturamento) as valor,
        prd_id 
    from venda 
    where 
        vnd_dt between @startDate and @endDate 
        and dst_id=@dst_id 
    group by 
        prd_id 
    order by 
        qtd desc
    limit 10
) valores;


SELECT 
    VendaComp.*
    , produto.prd_nome as Produto
FROM (
    SELECT
        SUM(vnd_quantidade) as quantidade,
        (SUM(vnd_faturamento) / @total1 * 100) as porcentagem_tooltip,
        SUM(vnd_faturamento) as faturamento,
        (SUM(vnd_faturamento) / @total2 * 100) as total_grafico,
        venda.prd_id
    from 
        venda 
    where 
        vnd_dt between @startDate and @endDate 
        and dst_id=@dst_id
    group by 
        prd_id 
    limit 10
) VendaComp
    INNER JOIN produto 
    ON(produto.prd_id=VendaComp.prd_id)
order by 
    porcentagem_tooltip desc, 
    quantidade desc,
    faturamento desc;

Using only one SQL, with SubQuery

set @startDate :='2015-01-03';
set @endDate :='2015-05-31';
set @dst_id:=1;

SELECT 
    VendaComp.quantidade
    , (VendaComp.faturamento / VendaComp.total1 * 100) porcentagem_tooltip
    , VendaComp.faturamento
    , (VendaComp.faturamento / VendaComp.total2 * 100) total_grafico
    , VendaComp.prd_id
    , produto.prd_nome as Produto
FROM (
    SELECT 
        *,
        (SELECT 
           SUM('vnd_faturamento')
         from venda 
            where 
                vnd_dt between @startDate and @endDate 
                and dst_id=@dst_id
        ) AS total1,
        (SELECT
            SUM(valores.valor)
        from (
                SELECT 
                    sum(venda.vnd_quantidade) as qtd,
                    sum(venda.vnd_faturamento) as valor,
                    prd_id 
                from venda 
                where 
                    vnd_dt between @startDate and @endDate 
                    and dst_id=@dst_id 
                group by 
                    prd_id 
                order by 
                    qtd desc
                limit 10
            ) valores
        ) total2
    FROM (
        SELECT
            SUM(vnd_quantidade) as quantidade,
            SUM(vnd_faturamento) as faturamento,
            venda.prd_id
        from 
            venda 
        where 
            vnd_dt between @startDate and @endDate 
            and dst_id=@dst_id
        group by 
            prd_id 
        limit 10
    ) T1
) VendaComp
    INNER JOIN produto 
    ON(produto.prd_id=VendaComp.prd_id)
ORDER BY
    porcentagem_tooltip desc, 
    quantidade desc,
    faturamento desc;