我有一个遗留的InnoDB表格Listing，其中包含带有纬度/经度的商家信息。给定输入的纬度/经度（低于51.2167 / 4.41667），查询应该以距离（公里）为顺序返回前30个活动的、启用的、未删除的商家。通过与账户表进行连接，可以检查列表的有效性。
select
listing.*
from
listing listing ,
account account
where
listing.account_id = account.id
and listing.active = 1
and listing.approved = 1
and listing.deleted = 0
and listing.enabled = 1
and account.enabled = 1
and account.activated_by_user = 1
group by
listing.id
having
111.222569*degrees(acos(sin(radians(listing.latitude))*sin(radians( 51.2167)) +cos(radians(listing.latitude))*cos(radians( 51.2167))*cos(radians(listing.longitude - 4.41667)))) < 250
order by
111.222569*degrees(acos(sin(radians(listing.latitude))*sin(radians( 51.2167)) +cos(radians(listing.latitude))*cos(radians( 51.2167))*cos(radians(listing.longitude - 4.41667))))
limit 30;
表格“Listing”和“Account”都包含超过50,000行数据，但查询平均需要24秒才能完成。如果没有order by, 则只需要17秒。
我已经尝试在active、approved、deleted、enabled上设置一些索引。是否可以重写查询或添加某些索引以有效地执行此查询 - 而不更改表结构？
+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | listing | index_merge | FKB4DC521D9306A80C,listing_active,listing_approved,listing_enabled,listing_deleted,index_test_1 | listing_active,listing_approved,listing_enabled,listing_deleted | 1,1,1,1 | NULL | 3392 | Using intersect(listing_active,listing_approved,listing_enabled,listing_deleted); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | account | eq_ref | PRIMARY,account_enabled,account_activated_by_user,index_test_2 | PRIMARY | 8 | ctm.listing.account_id | 1 | Using where |
+----+-------------+---------+-------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------+------+--------------------------------------------------------------------------------------------------------------------------------+
非常感谢您的帮助。