Limit the number of results of a sub-query with UNION in real time for performance improvement

2

I need to fetch many values that require two queries with different conditions. To do this I use a UNION , but I need to set a limit of results.

Today this is working with a limit and offset as shown at the end of the example, but this limit is only handled when the internal query ends, and this is degrading the application performance, since in the internal query are returned results in the house of millions.

The page limit I need is 50 results. How could I apply the limit in the internal query?

Example query (summarized):

SELECT * 
FROM (
        SELECT
            values
        FROM table
            LEFT JOIN table on vl1 = vl2
        WHERE
            conditions

        UNION

        SELECT
            values
        FROM table2
            LEFT JOIN table2 on vl1 = vl2
        WHERE
            conditions

    ) as res 
WHERE conditions LIMIT 0, 50

Edit, complete query:

CREATE TEMPORARY TABLE tmp_comprovacoes
            (
                SELECT
                    pcoordenacao,
                    concodigo AS codigo, 
                    IF(concompetenciames IS NULL OR concompetenciames = '00', conlancamento, CONCAT(concompetenciaano, '-', concompetenciames,'-15')) AS data, 
                    pcodebito AS debito,
                    conapagar,
                    conagente AS agente, 
                    icovalortotal AS valor, 
                    icocodigo,
                    'Não material' AS movimento, 
                    concolaborador AS colaborador,
                    pconome,
                    claplanodeconta,
                    pcodebito,
                    clanome,
                    clacodigo,
                    concentrodecusto AS centrodecusto,
                    IF(concompetenciames IS NULL OR concompetenciames = '00', DATE_FORMAT(conlancamento, '%d/%m/%Y'), CONCAT(concompetenciames, '/' ,concompetenciaano)) AS competencia,
                    -- Guarda a requisicão da conta vinculada ao reqcodigo;
                    conrequisicao
                FROM
                    itens_conta
                    LEFT JOIN conta ON icoconta = concodigo
                    LEFT JOIN requisicao ON reqcodigo = conrequisicao
                    LEFT JOIN centro_custo AS cec1 ON concentrodecusto = cec1.ceccodigo
                    LEFT JOIN esquema ON esqcodigo = cec1.cecesquema
                    LEFT JOIN usuario_grupo ON cec1.cecgrupo = ugrgrupo
                    LEFT JOIN classificacao ON icoclassificacao = clacodigo
                    LEFT JOIN plano_conta ON claplanodeconta = pcocodigo
                    -- entra na tabela comprovante e filtra os comprovantes que tem a mesma requisição e estão ativos;
                    LEFT JOIN comprovante ON cmprequisicao = reqcodigo AND cmpativo = 1

                WHERE
                    contipo = 'B'
                    AND concodigo IS NOT NULL
                    AND (pcocodigo != 14 AND pcocodigo != 15 AND pcocodigo != 13) 
                    AND icoativo = 1 
                    AND icoconta != 0 
                    AND concentrodecusto != 0 
                    AND icoclassificacao != 0
                    AND conrequisicao != 0  
                    AND ugrativo = 1 
                    -- Filtro para Favorecido (Comprovações), se for ativado != 0 ou = Agente;
                    {$where_comp}
                    AND {$portador1}
                    AND {$sem_fluxo}
                    AND {$where1}
                    AND ugrusuario = '".COD_USUARIO."'
            )
            UNION
            (
                SELECT
                    pcoordenacao,
                    concodigo AS codigo, 
                    IF(concompetenciames IS NULL OR concompetenciames = '00', conlancamento, CONCAT(concompetenciaano, '-', concompetenciames,'-15')) AS data, 
                    pcodebito AS debito,
                    conapagar,
                    conagente AS agente, 
                    icovalortotal AS valor, 
                    icocodigo,
                    'Não material' AS movimento, 
                    concolaborador AS colaborador,
                    pconome,
                    claplanodeconta,
                    pcodebito,
                    clanome,
                    clacodigo,
                    concentrodecusto AS centrodecusto,
                    IF(concompetenciames IS NULL OR concompetenciames = '00', DATE_FORMAT(conlancamento, '%d/%m/%Y'), CONCAT(concompetenciames, '/' ,concompetenciaano)) AS competencia,
                    conrequisicao
                FROM
                    itens_conta
                    LEFT JOIN conta ON icoconta = concodigo
                    LEFT JOIN centro_custo AS cec1 ON concentrodecusto = cec1.ceccodigo
                    LEFT JOIN esquema ON esqcodigo = cec1.cecesquema
                    LEFT JOIN usuario_grupo ON cec1.cecgrupo = ugrgrupo
                    LEFT JOIN classificacao ON icoclassificacao = clacodigo
                    LEFT JOIN plano_conta ON claplanodeconta = pcocodigo
                    LEFT JOIN requisicao ON reqcodigo = conrequisicao
                    -- entra na tabela comprovante e filtra os comprovantes que tem a mesma requisição e estão ativos
                    LEFT JOIN comprovante ON cmprequisicao = reqcodigo AND cmpativo = 1
                WHERE
                    conportador != 0
                    AND concodigo IS NOT NULL
                    AND (pcocodigo != 14 AND pcocodigo != 15 AND pcocodigo != 13) 
                    AND icoativo = 1 
                    AND icoconta != 0 
                    AND concentrodecusto != 0 
                    AND icoclassificacao != 0 
                    AND ugrativo = 1 
                    -- Filtro para Favorecido (Comprovações), se for ativado != 0 ou = Agente
                    {$where_comp}
                    AND ugrusuario = '".COD_USUARIO."'
                    AND {$portador2}
                    AND {$com_fluxo}
                    AND {$where1}
            )
            ORDER BY
                pcoordenacao, pconome DESC, clanome, data
    
asked by anonymous 11.05.2018 / 22:04

1 answer

2

By analyzing your complete query you do not need to use UNION. Notice that the data selected in the two queries are the same (Select the same table_item items and the same joins).

So if we execute the queries without the filtering conditions, the same data will be returned.

Turning only one OR logical condition. Where do you want to bring the records that respect the conditions of query1 OR that respect the conditions of query2.

This is the final query:

SELECT
    pcoordenacao,
    concodigo AS codigo, 
    IF(concompetenciames IS NULL OR concompetenciames = '00', conlancamento, CONCAT(concompetenciaano, '-', concompetenciames,'-15')) AS data, 
    pcodebito AS debito,
    conapagar,
    conagente AS agente, 
    icovalortotal AS valor, 
    icocodigo,
    'Não material' AS movimento, 
    concolaborador AS colaborador,
    pconome,
    claplanodeconta,
    pcodebito,
    clanome,
    clacodigo,
    concentrodecusto AS centrodecusto,
    IF(concompetenciames IS NULL OR concompetenciames = '00', DATE_FORMAT(conlancamento, '%d/%m/%Y'), CONCAT(concompetenciames, '/' ,concompetenciaano)) AS competencia,
    -- Guarda a requisicão da conta vinculada ao reqcodigo;
    conrequisicao
FROM
    itens_conta
    LEFT JOIN conta ON icoconta = concodigo
    LEFT JOIN requisicao ON reqcodigo = conrequisicao
    LEFT JOIN centro_custo AS cec1 ON concentrodecusto = cec1.ceccodigo
    LEFT JOIN esquema ON esqcodigo = cec1.cecesquema
    LEFT JOIN usuario_grupo ON cec1.cecgrupo = ugrgrupo
    LEFT JOIN classificacao ON icoclassificacao = clacodigo
    LEFT JOIN plano_conta ON claplanodeconta = pcocodigo
    -- entra na tabela comprovante e filtra os comprovantes que tem a mesma requisição e estão ativos;
    LEFT JOIN comprovante ON cmprequisicao = reqcodigo AND cmpativo = 1

WHERE
    (
        contipo = 'B'
        AND concodigo IS NOT NULL
        AND (pcocodigo != 14 AND pcocodigo != 15 AND pcocodigo != 13) 
        AND icoativo = 1 
        AND icoconta != 0 
        AND concentrodecusto != 0 
        AND icoclassificacao != 0
        AND conrequisicao != 0  
        AND ugrativo = 1 
        -- Filtro para Favorecido (Comprovações), se for ativado != 0 ou = Agente;
        {$where_comp}
        AND {$portador1}
        AND {$sem_fluxo}
        AND {$where1}
        AND ugrusuario = '".COD_USUARIO."'
    )
    OR
    (
        conportador != 0
        AND concodigo IS NOT NULL
        AND (pcocodigo != 14 AND pcocodigo != 15 AND pcocodigo != 13) 
        AND icoativo = 1 
        AND icoconta != 0 
        AND concentrodecusto != 0 
        AND icoclassificacao != 0 
        AND ugrativo = 1 
        -- Filtro para Favorecido (Comprovações), se for ativado != 0 ou = Agente
        {$where_comp}
        AND ugrusuario = '".COD_USUARIO."'
        AND {$portador2}
        AND {$com_fluxo}
        AND {$where1}
    )
    
18.05.2018 / 03:18