MYSQ查询问题
昨天问的问题,今天又难到了,哎,数据库学的一塌糊涂
map17表结构你:mysql> DESC MAP17;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| col | int(5) | NO | MUL | 0 | |
| row | int(5) | NO | MUL | 0 | |
| lat | double | NO | | 0 | |
| lon | double | NO | | 0 | |
| x | double | YES | | NULL | |
| y | double | YES | | NULL | |
| picname | varchar(20) | NO | | NULL | |
| pic | blob | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
9 rows in set
ysql> show index from map17;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| map17 | 0 | PRIMARY | 1 | id | A | 241527 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 1 | row | A | 86 | NULL | NULL | | BTREE | |
| map17 | 1 | row_col | 2 | col | A | 80509 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 1 | col | A | 1901 | NULL | NULL | | BTREE | |
| map17 | 1 | col_row | 2 | row | A | 80509 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set
select row,col,pic from map17 where row< 8 and col <8;
时间:4547毫秒,太慢了
mysql> explain select row,col,pic from map17 where row< 8 and col <8;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map17 | range | row_col,col_row | col_row | 4 | NULL | 336 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
select row,col,pic from map17 where row>=30 and row<38 and col>=108 and col <116
时间为:3703(第一次查询) 第二次查询(时间为:578)
mysql> explain select row,col,pic from map17 where row>=30 and row<38 and col>=108 and col <116;
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | map17 | range | row_col,col_row | col_row | 8 | NULL | 304 | Using where |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set
求大神解释,怎么提高查询速度啊(只需要64条记录,为什么这么慢)