我有一个MySQL的2查询联合,在索引列上做ORDER BY。在性能方面,分别按每个联合查询排序,还是像我目前拥有的那样在联合的末尾排序,哪种方式更好?
(SELECT pep.P_Surname, pep.P_FirstName, pep.P_ID, adm.Loc_ID AS lid
FROM lup_people pep
JOIN tr_adm adm ON pep.P_ID=adm.P_ID
WHERE (P_FirstName LIKE 'g%'
OR P_Surname LIKE 'g%')
AND pep.active = 'Y')
UNION
(SELECT pep.P_Surname, pep.P_FirstName, pep.P_ID, vil.Loc_ID AS lid
FROM lup_people pep
JOIN tr_village vil ON (pep.P_ID=vil.P1_ID OR pep.P_ID=vil.P2_ID)
WHERE (P_FirstName LIKE 'g%'
OR P_Surname LIKE 'g%')
AND pep.active = 'Y')
ORDER BY P_Surname,P_FirstName LIMIT 50
和
(SELECT pep.P_Surname, pep.P_FirstName, pep.P_ID, adm.Loc_ID AS lid
FROM lup_people pep
JOIN tr_adm adm ON pep.P_ID=adm.P_ID
WHERE (P_FirstName LIKE 'g%'
OR P_Surname LIKE 'g%')
AND pep.active = 'Y' ORDER BY P_Surname,P_FirstName LIMIT 50)
UNION
(SELECT pep.P_Surname, pep.P_FirstName, pep.P_ID, vil.Loc_ID AS lid
FROM lup_people pep
JOIN tr_village vil ON (pep.P_ID=vil.P1_ID OR pep.P_ID=vil.P2_ID)
WHERE (P_FirstName LIKE 'g%'
OR P_Surname LIKE 'g%')
AND pep.active = 'Y' ORDER BY P_Surname,P_FirstName LIMIT 50)
(1)您应该使用union all
,除非您打算使用union
来删除副本。
(2)查询不同。第一个返回100行。第二个返回50行。
(3)你不应该依赖于union
查询结果的顺序。如果您想要一个特定顺序的结果,那么在union
之后使用order by
。
(4)很可能,最快的方法是使用left outer join
。它看起来像这样:
SELECT pep.P_Surname, pep.P_FirstName, pep.P_ID,
coalesce(adm.Loc_ID, vil.loc_id, vil2.loc_id) AS lid
FROM lup_people pep LEFT JOIN
tr_adm adm
ON pep.P_ID = adm.P_ID LEFT JOIN
tr_village vil
ON pep.P_ID = vil.P1_ID LEFT JOIN
tr_village vil2
ON pep.P_ID = vil2.P2_ID
WHERE (P_FirstName LIKE 'g%' OR P_Surname LIKE 'g%') AND pep.active = 'Y'
ORDER BY P_Surname, P_FirstName
LIMIT 50;
根据数据量的不同,这两种情况都可能发生。你面临的问题是这样的。在您的第一个查询中,您正在执行select-from获取所有符合条件的可能答案,之后,然后应用order by子句,最后是限制。因此,如果您有1000条符合条件的记录,您仍然在浏览它们,排序,然后获得前50条。
第二个将至少将各自的结果集从每个减少到50个,留下最多100条记录。