求指导，用wex5做一个和地理位置有关的web，查询数据库时....

ufo1982ufo

求指导，用wex5做一个和地理位置有关的web，查询数据库时如何优化？
数据共100万条，使用between和不使用between与使用spatial和不使用spatial的结果好像刚好相反，本人新手，求问究竟是如何使用好呢？

不带索引如下：
SELECT name, AsText(location) FROM Points WHERE X(location) < 103.252243 and X(location)>10.055963 and Y(location) > 23.275588 and Y(location) < 123.455768;
/* Affected rows: 0  已找到记录: 96,572  警告: 0  持续时间 1 query: 0.000 sec. (+ 0.922 sec. network) */
SELECT name, AsText(location) FROM Points WHERE X(location) between 10.055963 and  103.252243 and Y(location) between 23.275588 and  123.455768;
/* Affected rows: 0  已找到记录: 96,572  警告: 0  持续时间 1 query: 0.016 sec. (+ 1.406 sec. network) */

增加spatial索引：
ALTER TABLE `points`
        ADD SPATIAL INDEX `location` (`location`);
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='12';
SHOW TABLE STATUS FROM `12`;
SHOW FUNCTION STATUS WHERE `Db`='12';
SHOW PROCEDURE STATUS WHERE `Db`='12';
SHOW TRIGGERS FROM `12`;
SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='12';
SHOW CREATE TABLE `12`.`points`;
/* 进入会话 "X5" */
SHOW CREATE TABLE `12`.`points`;

查询结果如下：
SELECT name, AsText(location) FROM Points WHERE X(location) < 103.252243 and X(location)>10.055963 and Y(location) > 23.275588 and Y(location) < 123.455768;
/* Affected rows: 0  已找到记录: 96,572  警告: 0  持续时间 1 query: 0.015 sec. (+ 1.032 sec. network) */
SELECT name, AsText(location) FROM Points WHERE X(location) between 10.055963 and  103.252243 and Y(location) between 23.275588 and  123.455768;
/* Affected rows: 0  已找到记录: 96,572  警告: 0  持续时间 1 query: 0.000 sec. (+ 1.218 sec. network) */

大雁子

建议上网查询一下SQL语句优化

ufo1982ufo

大雁子 发表于 2016-11-1 13:54
建议上网查询一下SQL语句优化

谢谢！
参考了2篇文章：
http://www.111cn.net/database/mysql/45273.htm
http://blog.csdn.net/yaofuyuan/article/details/6080836
感觉了<>性能更优一些，选择了<>代替了between and，但是又放弃了spatial空间索引，本身就是地理位置有关的，担心以后是硬伤啊！