MySQL Spatial密钥无法正常工作
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL Spatial密钥无法正常工作，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4736字，纯文字阅读大概需要7分钟。
内容图文
我有两个包含链接事件和事件时间的数据库表：
mysql> show create table link_events
*************************** 1. row ***************************
Table: link_events
Create Table: CREATE TABLE `link_events` (
`device_name` varchar(32) DEFAULT NULL,
`link_name` varchar(32) DEFAULT NULL,
`event_type` varchar(32) DEFAULT NULL,
`link_event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`link_event_id`),
UNIQUE KEY `link_events` (`device_name`,`link_name`,`event_type`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table event_times
*************************** 1. row ***************************
Table: event_times
Create Table: CREATE TABLE `event_times` (
`down_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`up_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`span` geometry NOT NULL,
`link_event_id` int(10) unsigned NOT NULL,
`event_time_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`event_time_id`),
SPATIAL KEY `span` (`span`),
KEY `fk_link_events` (`link_event_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
INSERT INTO link_events(device_name,link_name, event_type)
VALUES('d1','l1','e1') on duplicate key update link_event_id = link_event_id;
INSERT INTO link_events(device_name,link_name, event_type)
VALUES('d1','l1','e2') on duplicate key update link_event_id = link_event_id;
INSERT INTO event_times(down_time, up_time, span, link_event_id)
VALUES('2015-01-01 00:00:04', '2015-01-01 00:00:08',
linestring(point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:08','%Y-%m-%d %H:%i:%s')),0),point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:04','%Y-%m-%d %H:%i:%s')),0)),
(SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1'));
INSERT INTO event_times(down_time, up_time, span, link_event_id)
VALUES('2015-01-01 00:00:07', '2015-01-01 00:00:09',
linestring(point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:09','%Y-%m-%d %H:%i:%s')),0),point(UNIX_TIMESTAMP(STR_TO_DATE('2015-01-01 00:00:07','%Y-%m-%d %H:%i:%s')),0)),
(SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2'));
我想找到时间重叠的link_events并使用以下查询：
EXPLAIN
SELECT
CONCAT('Link1','-', 'Link2') overlaps,
GREATEST(a.down_time,b.down_time) AS downtime,
LEAST(a.up_time,b.up_time) AS uptime,
TIME_TO_SEC(TIMEDIFF( LEAST(a.up_time,b.up_time),
GREATEST(a.down_time,b.down_time))) AS duration
FROM event_times a
JOIN event_times b
ON Intersects (a.span, b.span)
WHERE a.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1')
AND b.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2');
+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
| 1 | PRIMARY | a | ref | span,fk_link_events | fk_link_events | 4 | const | 1 | Using where |
| 1 | PRIMARY | b | ref | span,fk_link_events | fk_link_events | 4 | const | 1 | Using where |
| 3 | SUBQUERY | link_events | const | link_events | link_events | 297 | const,const,const | 1 | NULL |
| 2 | SUBQUERY | link_events | const | link_events | link_events | 297 | const,const,const | 1 | NULL |
+----+-------------+-------------+-------+---------------------+----------------+---------+-------------------+------+-------------+
4 rows in set (0.00 sec)
如何让span列成为关键以优化查询时间？
link_events包含2k行,而event_times包含900k行.
提前谢谢了 ！
解决方法:
要强制在查询上使用索引,请使用表名指定它：
SELECT
CONCAT('Link1','-', 'Link2') overlaps,
GREATEST(a.down_time,b.down_time) AS downtime,
LEAST(a.up_time,b.up_time) AS uptime,
TIME_TO_SEC(TIMEDIFF( LEAST(a.up_time,b.up_time),
GREATEST(a.down_time,b.down_time))) AS duration
FROM event_times a USE KEY (span)
JOIN event_times b USE KEY (span)
ON Intersects (a.span, b.span)
WHERE a.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e1')
AND b.link_event_id = (SELECT link_event_id FROM link_events WHERE device_name = 'd1' AND link_name = 'l1' AND event_type = 'e2');
我会说我从来没有机会再次猜测默认索引,所以没有经验实际使用它！
内容总结
以上是互联网集市为您收集整理的MySQL Spatial密钥无法正常工作全部内容，希望文章能够帮你解决MySQL Spatial密钥无法正常工作所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】