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

Question

I need to search many values that need two query’s with different conditions. To do this I use one 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 dealt with when the internal query ends, and this is degrading the performance of the application, because in the internal query results are returned in the millions.

The pagination limit I need is 50 results. How I could apply the limit in internal consultation?

Example of the 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

Answer 1

Analyzing your complete query does not need you to use UNION. Note that the data selected in the two querys are the same (Selects the same table itens_conta and the same joins).

So if we run the querys without the filtering conditions the same data will be returned.

Becoming only a logical condition of type OR. Where you want to bring the records that comply with Query1 OR conditions that respect the conditions of the query2.

Thus remaining 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}
    )