Mysql 不使用我的索引
我正在使用“解释”来查看为什么我的查询会命中数据库中的每一行,但我不明白为什么会这样做。
有人可以看一下并给我提示我缺少什么吗?
我的数据库是 MyISAM,我使用 Mysql 5.1,PHP5
这是我的表:
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` bigint(20) NOT NULL auto_increment,
`name` varchar(40) default NULL,
`city` varchar(90) default NULL,
`latitude` float NOT NULL default '0',
`longitude` float NOT NULL default '0',
PRIMARY KEY (`user_id`),
UNIQUE KEY `name` (`name`),
KEY `Radius Search` (`latitude`,`longitude`),
KEY `Radius 2` (`longitude`,`latitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=38666 ;
这是我的查询:
$query =
"SELECT
name, city
FROM
users
WHERE
(
(69.1 * (latitude - " . $user->latitude . ")) *
(69.1 * (latitude - " . $user->latitude . "))
) + (
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) *
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
) < " . pow($radius, 2) . "
ORDER BY
(
(69.1 * (latitude - " . $user->latitude . ")) *
(69.1 * (latitude - " . $user->latitude . "))
) + (
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) *
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
) ASC";
最后,我的解释...
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ALL NULL NULL NULL NULL 38665 Using where; Using filesort
I am using 'explain' to view why my query is hitting every single row in my database, and I do not understand why it is doing so.
Could someone take a look and give me a hint what I am missing?
My database is MyISAM, and I am using Mysql 5.1, PHP5
Here is my table:
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`user_id` bigint(20) NOT NULL auto_increment,
`name` varchar(40) default NULL,
`city` varchar(90) default NULL,
`latitude` float NOT NULL default '0',
`longitude` float NOT NULL default '0',
PRIMARY KEY (`user_id`),
UNIQUE KEY `name` (`name`),
KEY `Radius Search` (`latitude`,`longitude`),
KEY `Radius 2` (`longitude`,`latitude`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=38666 ;
Here is my query:
$query =
"SELECT
name, city
FROM
users
WHERE
(
(69.1 * (latitude - " . $user->latitude . ")) *
(69.1 * (latitude - " . $user->latitude . "))
) + (
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) *
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
) < " . pow($radius, 2) . "
ORDER BY
(
(69.1 * (latitude - " . $user->latitude . ")) *
(69.1 * (latitude - " . $user->latitude . "))
) + (
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3)) *
(69.1 * (longitude - " . $user->longitude . ") * COS(" . $user->latitude . " / 57.3))
) ASC";
And finally, my explain...
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ALL NULL NULL NULL NULL 38665 Using where; Using filesort
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
究竟应该如何使用“纬度”上的排序索引来更有效地评估您的 where 子句? ...当在 where 子句的表达式中使用字段时,索引通常不适用。
此外,在这种情况下,我不确定您是否可以重写该子句来应用索引,这种情况比较罕见。
附录:
看看您实际想要做什么,也许您可以在用户坐标周围使用固定大小的纬度/经度框,以便您可以在纬度和经度上使用
BETWEEN
子句。 然后,您可以保持ORDER BY
与现在一样复杂,并在最接近其他用户的“x
”之后截止。那么你可能永远不会注意到火柴实际上是在一个盒子里,而不是在你正在考虑的一个圆形半径内。
How exactly is it supposed to use a sorted index on 'latitude' to more efficiently evaluate your where clause? ... When a field is used in an expression in the where clause, indexes typically do not apply.
Moreover, in this case I'm not sure you can even rewrite the clause to make the index apply, which is a little rarer.
Addendum:
Looking at what you are actually trying to do, maybe you can use a fixed-size lat/long box around the users' coordinate, so that you can use
BETWEEN
clauses on lat and long. Then you can keep theORDER BY
complex as it is now, and cut-off after the 'x
' closest other users.Then you may never notice that the matches are actually in a box rather than a circular radius from the one you are considering.
最有可能是因为您正在过滤 EXPRESSION 而不是索引列。
MySQL 对列而不是表达式建立索引。 这里有几个选项:
如果这是不可能的,那么请考虑创建一个非常快速的查找表,并对其进行连接。 例如:
因为它将是一个很小的固定宽度表,所以即使使用全表扫描,您也应该能够非常快速地查询它。 只需加入反对它即可获得您想要的用户。
(注意:在进行这些长度之前检查性能要求,因为您可能根本不需要它)
Most likely it is because you are filtering on an EXPRESSION rather than an indexed column.
MySQL indexes on columns, not expressions. You have a couple of options here:
If that is not possible, then consider creating a very fast lookup table, and join against it. For example:
Because it will be a tiny fixed width table, you should be able to query it very fast even with a full table scan. Just join against it to get the users you want.
(note: check performance requirements before going to these lengths, as you may not need it at all)
您实际上并没有将任何东西与纬度或经度进行比较。 为了让 indes 工作,mysql 需要设置一些东西来达到 latitude = (或 <> 之间,等等)。
尝试重写查询,使其读取
$lat_low AND $lat_hi 之间的 WHERE 纬度和 $long_low AND $long_hi 之间的经度
You are not actually comparing anything to latitude or longitude. For indes to work mysql nees to se something to the effect latitude = (or < > between, etc).
Try rewriting the query so it reads
WHERE latitude between $lat_low AND $lat_hi and longitude between $long_low AND $long_hi