mysql union null_mysql – 在UNION中使用Order By NULL

我有一个查询(见下文),我有一个自定义开发的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 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不起作用.

任何帮助将不胜感激.