Sql query performance is low-CodePudding

I would like to optimize my sql query. When I put a lot of data in the investors_diversificacao table it gets very slow and doesn't load. But with little data in the investors_diversificacao table it can load the data.

I would like help to optimize query response time.

sql query:

SELECT investors_positivador.cod_cliente, investors_saldo_financeiro.nome_cliente, investors_base_assessores.squad, investors_base_assessores.nome_investor, investors_saldo_financeiro.saldo_d0, 
                  SUM(CASE WHEN investors_posicao_geral.vencimento <= @vencimento THEN investors_posicao_geral.financeiro ELSE 0 END) AS vencimentos_ate_data, 
                  ROUND(SUM(CASE WHEN investors_diversificacao.produto = 'Fundos' THEN investors_diversificacao.net / 6 ELSE 0 END), 2) AS fundos_ate_data, investors_guia_fundos.liquidez_total, investors_positivador.contatar_liquidity_map, 
                  investors_positivador.id, investors_posicao_geral.vencimento, investors_base_assessores.nome_assessor
FROM     investors_positivador 
    INNER JOIN investors_saldo_financeiro ON cod_cliente = investors_saldo_financeiro.cod_cliente 
    INNER JOIN investors_base_assessores ON cod_assessor = investors_base_assessores.cod_assessor 
    INNER JOIN investors_posicao_geral ON investors_positivador.cod_cliente = investors_posicao_geral.cod_cliente 
    LEFT OUTER JOIN investors_diversificacao ON investors_positivador.cod_cliente = investors_diversificacao.cod_cliente 
    LEFT OUTER JOIN investors_guia_fundos ON investors_diversificacao.cnpj = investors_guia_fundos.cnpj
WHERE  (investors_base_assessores.nome_investor = @investor_nome) 
AND (investors_saldo_financeiro.saldo_d0 > 0) 
    OR (investors_base_assessores.nome_investor = @investor_nome) 
AND (investors_posicao_geral.financeiro > 0) 
    OR (investors_base_assessores.nome_investor = @investor_nome) 
AND (investors_diversificacao.net > 0)
GROUP BY investors_positivador.cod_cliente

CodePudding user response：

WHen mixing AND with OR its always necessary to bracket correctly, and not use unnecessary bracketing

WHERE  investors_base_assessores.nome_investor = @investor_nome
AND (
        investors_saldo_financeiro.saldo_d0 > 0 
        OR 
        investors_base_assessores.nome_investor = @investor_nome
    ) 
AND (
        investors_posicao_geral.financeiro > 0
        OR 
        investors_base_assessores.nome_investor = @investor_nome
    ) 
AND investors_diversificacao.net > 0

CodePudding user response：

Some of these indexes may help:

investors_positivador:       INDEX(cod_cliente,  contatar_liquidity_map, id)
investors_saldo_financeiro:  INDEX(cod_cliente, saldo_d0,  nome_cliente)
investors_base_assessores:   INDEX(nome_investor, cod_assessor,  squad, nome_assessor)
investors_posicao_geral:     INDEX(cod_cliente, financeiro,  vencimento)
investors_diversificacao:    INDEX(cod_cliente, net,  produto, cnpj)
investors_guia_fundos:       INDEX(cnpj,  liquidez_total)

Are you sure you want that WHERE clause? All the ORs are irrelevant, and Riggs' version simplifies to just

WHERE  investors_base_assessores.nome_investor = @investor_nome
  AND  investors_diversificacao.net > 0

Don't round before summing; you will get extra rounding errors:

ROUND(SUM(IF(investors_diversificacao.produto = 'Fundos',
             investors_diversificacao.net, 0
            )
         ) / 6     -- divide after summing and before rounding
  , 2) AS fundos_ate_data