如何索引这个查询？
编制此查询时遇到问题：
SELECT *,
(ROUND(SQRT(
POW(LEAST(ABS(-12 - wdata.x),
ABS(401 - ABS(-12 - wdata.x))), 2) +
POW(LEAST(ABS(45 - wdata.y),
ABS(401 - ABS(45 - wdata.y))), 2)),3)
) AS distance
FROM odata
LEFT JOIN wdata ON wdata.id=odata.vref
WHERE TRUE
HAVING distance<4.9497474683058326708059105347339
ORDER BY distance
LIMIT 30
其结果是：
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+ | 1 | SIMPLE | odata | ALL | NULL | NULL | NULL | NULL | 19118 | Using temporary; Using filesort | | 1 | SIMPLE | wdata | eq_ref | PRIMARY | PRIMARY | 4 | mytravia_1000-14.odata.vref | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+---------------------------------+ 2 rows in set (0.00 sec)
我知道它显示0.00秒是执行时间，但此查询将运行许多次，它显示它会减慢我的数据库，我不知道为什么！
每次我看到这行查询的行都是459448，所以在某些原因下它对我的工作非常不利。
任何人都可以提出建议吗？ 我如何才能为odata表制作合适的索引？ 或者我可以使用子查询来修复它？
这些表格是：
解释odata：
vref int(10) unsigned NO PRI NULL type tinyint(4) NO NULL conqured mediumint(8) unsigned NO NULL wood float(12,2) NO NULL iron float(12,2) NO NULL clay float(12,2) NO NULL woodp float(12,2) NO NULL ironp float(12,2) NO NULL clayp float(12,2) NO NULL maxstore mediumint(8) unsigned NO NULL crop float(12,2) NO NULL cropp float(12,2) NO NULL maxcrop mediumint(8) unsigned NO NULL lasttrain int(10) unsigned NO NULL lastfarmed int(10) unsigned NO NULL lastupdated int(10) unsigned NO NULL loyalty tinyint(4) NO 100 owner smallint(5) unsigned NO 2 name char(45) NO Oasis
并解释wdata：
id int(10) unsigned NO PRI NULL auto_increment fieldtype tinyint(3) NO NULL oasistype tinyint(3) NO NULL x smallint(5) NO MUL NULL y smallint(5) NO MUL NULL occupied tinyint(4) NO NULL image char(12) NO MUL NULL pos tinyint(3) NO MUL NULL
我不得不说wdata.id和odata.vref已经索引！
表结构 - >
CREATE TABLE IF NOT EXISTS `odata` ( `vref` int(10) unsigned NOT NULL, `type` tinyint(4) NOT NULL, `conqured` mediumint(8) unsigned NOT NULL, `wood` float(12,2) NOT NULL, `iron` float(12,2) NOT NULL, `clay` float(12,2) NOT NULL, `woodp` float(12,2) NOT NULL, `ironp` float(12,2) NOT NULL, `clayp` float(12,2) NOT NULL, `maxstore` mediumint(8) unsigned NOT NULL, `crop` float(12,2) NOT NULL, `cropp` float(12,2) NOT NULL, `maxcrop` mediumint(8) unsigned NOT NULL, `lasttrain` int(10) unsigned NOT NULL, `lastfarmed` int(10) unsigned NOT NULL, `lastupdated` int(10) unsigned NOT NULL, `loyalty` tinyint(4) NOT NULL DEFAULT '100', `owner` smallint(5) unsigned NOT NULL DEFAULT '2', `name` char(45) NOT NULL DEFAULT 'Unoccupied Oasis', PRIMARY KEY (`vref`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
对于wdata是 - >
CREATE TABLE IF NOT EXISTS `wdata` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `fieldtype` tinyint(3) NOT NULL, `oasistype` tinyint(3) NOT NULL, `x` smallint(5) NOT NULL, `y` smallint(5) NOT NULL, `occupied` tinyint(4) NOT NULL, `image` char(12) NOT NULL, `pos` tinyint(3) NOT NULL, PRIMARY KEY (`id`), KEY `x` (`x`), KEY `y` (`y`), KEY `image` (`image`), KEY `pos` (`pos`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=160802 ;
最好的祝福。
没有索引可以加快查询的速度。 它目前必须评估JOINing两个表的结果中每一行的SQRT 。
在进行任何JOINing之前，您将通过查找关闭30来获得一些改进：
SELECT *, distance
FROM ( SELECT id,
(ROUND(SQRT(
POW(LEAST(ABS(-12 - wdata.x),
ABS(401 - ABS(-12 - wdata.x))), 2) +
POW(LEAST(ABS(45 - wdata.y),
ABS(401 - ABS(45 - wdata.y))), 2)),3)
) AS distance
FROM wdata
HAVING distance<4.9497474683058326708059105347339
ORDER BY distance
LIMIT 30
) w
JOIN odata ON w.id=odata.vref
ORDER BY w.distance
这将需要id和vref索引。
下一个改进是将搜索限制在至少一个方向上：
AND x >= -12 - 4.94...
AND x <= -12 + 4.94...
并在wdata具有复合索引INDEX(x, id) 。 （对不起，我不知道“401”符合公式的地方。）
如果速度不够快，解决方案会变得更加复杂。
就像里克詹姆斯说的那样，你不能指定distance因为它是动态计算的。
这给你两个问题：1，如你所知，它很慢。 2，你在数据层进行逻辑计算，我不太喜欢。
我认为这里最好的解决方案是不要像你在做那样快速计算距离。 为什么在插入/更新x和/或y的同时将距离存储在wdata中？ 把它放在一个名为distance的列中。 然后，您可以对该列进行索引，一切都将非常快速。 此外，你不会重复计算一遍又一遍，使事情更有效率。 最后，您将能够移除数据层的计算并将其放到应用程序级别的更合适的位置。
CREATE TABLE IF NOT EXISTS `wdata` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fieldtype` tinyint(3) NOT NULL,
`oasistype` tinyint(3) NOT NULL,
`x` smallint(5) NOT NULL,
`y` smallint(5) NOT NULL,
`distance` decimal(32, 24) NOT NULL,
`occupied` tinyint(4) NOT NULL,
`image` char(12) NOT NULL,
`pos` tinyint(3) NOT NULL,
PRIMARY KEY (`id`),
KEY `x` (`x`),
KEY `y` (`y`),
KEY `distance` (`distance`),
KEY `image` (`image`),
KEY `pos` (`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=160802;
（距离的数据类型可以是任何你认为合适的数据类型，我使用decimal(32, 24) ，它将存储数字，最多24位，小数点右边，最多12位，根据需要进行调整。
然后你会改变你的插入是这样的：
（样本数据：
）
insert into wdata (fieldtype,oasistype,x,y,distance,occupied, image, pos)
values (1, 1, 10, 11, (ROUND(SQRT(
POW(LEAST(ABS(-12 - 10),
ABS(401 - ABS(-12 - 10))), 2) +
POW(LEAST(ABS(45 - 11),
ABS(401 - ABS(45 - 11))), 2)),3)
), 1, 'abcdefghijkl', 1)
你的选择语句是：
SELECT * FROM odata
LEFT JOIN wdata ON wdata.id=odata.vref
where wdata.distance<4.9497474683058326708059105347339
ORDER BY wdata.distance
LIMIT 30
如果wdata表中已经有一堆数据，并且不能新插入，则可以一次更新所有行（在为距离添加新列后）：
update wdata set distance =
(ROUND(SQRT(
POW(LEAST(ABS(-12 - x),
ABS(401 - ABS(-12 - x))), 2) +
POW(LEAST(ABS(45 - y),
ABS(401 - ABS(45 - y))), 2)),3))
另外值得注意的是，我会从MySQL中删除数学，并让您的应用程序执行此操作。
例如，在PHP中：
$distance = (round(sqrt(pow(min(abs(-12 - 10), abs(401 - abs(-12 - 10))), 2) + pow(min(abs(45 - 11), abs(401 - abs(45 - 11))), 2)),3));
$sql = "insert into wdata (fieldtype, oasistype, x, y, distance, occupied, image, pos)
values (1, 1, 10, 11, $distance, 1, 'abcdefghijkl', 1)";