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