当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Union alternate to make query more efficient. Mysql

问题描述:

Below is my query

 Select

count(t.prid)

from

(select

pr.prid

from

jcp

inner join pr ON pr.prid = jcp.prid

where

jcp.custid = 123 union select

pr.prid

from

jcl

inner join pr ON pr.prid = jcl.prid

where

jcl.custid = 123) as t

is there any way to make it more efficient? this query is inside some function and executing 1000s of time. so making it slow.

网友答案:

First of all, your query appears to be combining two very different types of data in your 'union' - the first part being the count of an ID, and the second being the literal ID - so I would question whether this is really doing what you intend it to do as written. However, just taking it at face value, you could eliminate the subquery in the first part as follows:

SELECT COUNT(pr.prid)
FROM jcp
    INNER JOIN pr 
    ON pr.prid = jcp.prid
WHERE jcp.custid = 123 

I can't say how much that would help your performance without knowing the context of your data, but it certainly wouldn't hurt.

Given the difference in the two data sets, it doesn't appear possible to avoid the union if you want to force these two different bits of data into the same column. If you were to put them into different columns, you could probably avoid the union.

分享给朋友:
您可能感兴趣的文章:
随机阅读: