mysql – 在UNION中使用Order By NULL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 在UNION中使用Order By NULL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8094字，纯文字阅读大概需要12分钟。
内容图文
我有一个查询(见下文),我有一个自定义开发的UDF,用于计算某些点是否在多边形(UNION中的第一个查询)或循环(UNION中的第二个查询)形状内.
select e.inquiry_match_type_id
, a.geo_boundary_id
, GeoBoundaryContains(c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, a.lat, a.lon, a.geo_boundary_vertex_id ) in_out
, e.inquiry_id
, e.external_id
, COALESCE(f.inquiry_device_id,0) inquiry_device_id
, b.external_info1
, b.external_info2
, b.geo_boundary_id
, b.geo_boundary_type_id
from geo_boundary_vertex a
join geo_boundary b on b.geo_boundary_id = a.geo_boundary_id
join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id
join trackpoint_index_mem d on d.trackpoint_index_id = c.trackpoint_index_id
join inquiry_mem e on e.inquiry_id = b.inquiry_id left
outer join inquiry_device_mem f on f.inquiry_id = e.inquiry_id and f.device_id = 3201
where d.trackpoint_index_id = 3127
and b.geo_boundary_type_id = 3
and e.expiration_date >= now()
group by
a.geo_boundary_id
UNION
select e.inquiry_match_type_id
, b.geo_boundary_id
, GeoBoundaryContains( c.tpi_geo_boundary_coverage_type_id, 29.287437, -95.055807, b.centroid_lat, b.centoid_lon, b.radius ) in_out
, e.inquiry_id
, e.external_id
, COALESCE(f.inquiry_device_id,0) inquiry_device_id
, b.external_info1
, b.external_info2
, b.geo_boundary_id
, b.geo_boundary_type_id
from geo_boundary b
join trackpoint_index_geo_boundary_mem c on c.geo_boundary_id = b.geo_boundary_id
join trackpoint_index_mem d on d.trackpoint_index_id = c.trackpoint_index_id
join inquiry_mem e on e.inquiry_id = b.inquiry_id
left outer join inquiry_device_mem f on f.inquiry_id = e.inquiry_id and f.device_id = 3201
where d.trackpoint_index_id = 3127
and b.geo_boundary_type_id = 2
and e.expiration_date >= now()
group by
b.geo_boundary_id
当我为查询运行解释时,我得到以下内容：
id select_type table type possible_keys key key_len ref rows Extra
------ -------------- ---------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ---------- ------------------------ ------- -------------------------------
1 PRIMARY d const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
1 PRIMARY c ref PRIMARY,fk_mtp_idx_geo_boundary_mtp_idx,fk_mtp_idx_geo_boundary_geo_boundary,fk_mtp_idx_geo_boundary_mtp_mem_idx,fk_mtp_idx_geo_boundary_geo_boundary_mem fk_mtp_idx_geo_boundary_mtp_idx 4 const 9
1 PRIMARY b eq_ref PRIMARY,fk_geo_boundary_inquiry,fk_geo_boundary_geo_boundary_type PRIMARY 4 gothim.c.geo_boundary_id 1 Using where
1 PRIMARY e eq_ref PRIMARY PRIMARY 4 gothim.b.inquiry_id 1 Using where
1 PRIMARY f ref fk_inquiry_device_mem_inquiry fk_inquiry_device_mem_inquiry 4 gothim.e.inquiry_id 2
1 PRIMARY a ref fk_geo_boundary_vertex_geo_boundary fk_geo_boundary_vertex_geo_boundary 4 gothim.b.geo_boundary_id 11 Using where
2 UNION d const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
2 UNION c ref PRIMARY,fk_mtp_idx_geo_boundary_mtp_idx,fk_mtp_idx_geo_boundary_geo_boundary,fk_mtp_idx_geo_boundary_mtp_mem_idx,fk_mtp_idx_geo_boundary_geo_boundary_mem fk_mtp_idx_geo_boundary_mtp_idx 4 const 9
2 UNION b eq_ref PRIMARY,fk_geo_boundary_inquiry,fk_geo_boundary_geo_boundary_type PRIMARY 4 gothim.c.geo_boundary_id 1 Using where
2 UNION e eq_ref PRIMARY PRIMARY 4 gothim.b.inquiry_id 1 Using where
2 UNION f ref fk_inquiry_device_mem_inquiry fk_inquiry_device_mem_inquiry 4 gothim.e.inquiry_id 2
(null) UNION RESULT <union1,2> ALL (null) (null) (null) (null) (null) Using filesort
12 record(s) selected [Fetch MetaData: 1ms] [Fetch Data: 5ms]
现在,我可以拆分查询并使用ORDER BY NULL技巧来删除文件,但是当我尝试将它添加到UNION的末尾时它不起作用.
我正在考虑将查询分成2个查询或者可能完全重写它不使用UNION(当然这有点困难).我反对的另一件事就是我们已经开始使用它了,我想限制更改 – 我本来希望能够在查询结束时添加ORDER BY NULL并完成它,但是它与UNION不起作用.
任何帮助将不胜感激.
最佳答案:
通常,ORDER BY可以用于UNION中的各个查询,如下所示：
(
SELECT *
FROM table1, …
GROUP BY
id
ORDER BY
NULL
)
UNION ALL
(
SELECT *
FROM table2, …
GROUP BY
id
ORDER BY
NULL
)
但是,作为docs状态：
However, use of
ORDER BYfor individualSELECTstatements implies nothing about the order in which the rows appear in the final result becauseUNIONby default produces an unordered set of rows. Therefore, the use ofORDER BYin this context is typically in conjunction withLIMIT, so that it is used to determine the subset of the selected rows to retrieve for theSELECT, even though it does not necessarily affect the order of those rows in the finalUNIONresult. IfORDER BYappears withoutLIMITin aSELECT, it is optimized away because it will have no effect anyway.
这当然是一个聪明的举动,但不是太聪明,因为他们忘了优化GROUP BY的排序行为.
现在,您应该为您的个人查询添加一个非常高的LIMIT：
(
SELECT *
FROM table1, …
GROUP BY
id
ORDER BY
NULL
LIMIT 100000000
)
UNION ALL
(
SELECT *
FROM table2, …
GROUP BY
id
ORDER BY
NULL
LIMIT 100000000
)
我将它作为一个错误发布到MySQL,希望他们能在下一个版本中修复它,但同时你可以使用这个解决方案.
请注意,类似的解决方案(使用TOP 100％)用于强制SQL Server 2000中的子查询的排序,但是,它在2005年停止工作(ORDER BY对于优化器的TOP 100％的子查询没有影响).
使用它是安全的,因为即使优化器行为在下一个版本中发生变化,它也不会破坏您的查询,但只会使它们像现在一样慢.
内容总结
以上是互联网集市为您收集整理的mysql – 在UNION中使用Order By NULL全部内容，希望文章能够帮你解决mysql – 在UNION中使用Order By NULL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。