Improve the performance of a query

Question

Improve the performance of a query

Asked 9 years, 4 months ago

Viewed 742 times

6

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


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’d like to avoid so many Subquerys.

My table is presenting 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;

3

It is complicated to indicate an improvement in the query without seeing at least the DDL of the tables.

– Daniel Omine

2015/06/29 at 12:40
4

The first thing to do is to check if the fields that are part of the sums, groups and conditions, are being indexed. Second, avoid LEFT JOIN, prefer INNER (if possible). Third, you can avoid order by in querys with group by and if you really need to, play them for the most external queries. Fourth, create functions to perform repetitive calculations, since SGDB will probably index this procedure only by changing the variables. This should help enough.

– Gildonei

2015/06/29 at 12:51
I put the tables to improve the question.

– Ivan Ferrer

2015/06/29 at 14:03
@Ivanferrer because the use of LEFT JOIN? Logically, there is no way to have sales without products. In my opinion you could already start by putting INNER JOIN in place of LEFT JOIN. Try to explain better what you really want to search for in Query.

– Marconi

2015/06/29 at 17:58
I did, but I’d like to avoid the excess of subquerys.

– Ivan Ferrer

2015/06/29 at 19:16
1

Include the Execution plan of your query. place a EXPLAIN before SELECT and go to [Edit] question.

– gmsantos

2015/07/24 at 19:17
@gmsantos put, and also put the tables.

– Ivan Ferrer

2015/07/24 at 22:10

Show 2 more comments

1 answer

Browser other questions tagged mysql sql mysql-workbench

You are not signed in. Login or sign up in order to post.

by Leandro Amorim • **1,865** points · Answer 1 · 2015-07-24T22:10:47+00:00

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

Edited

You can also improve performance by doing Join only at the end after 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;