昨天问的问题,今天又难到了,哎,数据库学的一塌糊涂
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 setselect 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 setselect 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条记录,为什么这么慢)性能 mysql select
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 setselect 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 setselect 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条记录,为什么这么慢)性能 mysql select
union all
select row,col,pic from map17 where row=7 and col <8
union all
select row,col,pic from map17 where row=6 and col <8
union all
select row,col,pic from map17 where row=5 and col <8
union all
select row,col,pic from map17 where row=4 and col <8
union all
select row,col,pic from map17 where row=3 and col <8
union all
select row,col,pic from map17 where row=2 and col <8
union all
select row,col,pic from map17 where row=1 and col <8
把 row>=30 and row<38 转化为UNION或者把 col>=108 and col <116 进行转化。
第一次时间为:2453 第二次时间为:2781还是慢啊
第一次时间为:2453 第二次时间为:2781还是慢啊map15上的索引是什么? 建议做测试的时候使用同个表,否则很容易导致判断结果混乱。
数据结构如下
id name area
01 a 河北
02 b 山东
03 c 河北
04 d 陕西
05 e 河北
06 f 山东也即每个用户都有id,name,area
现要求如下:
按地区聚合并统计用户数、显示各用户详细情况大概类似于下面的结果:
地区 总用户数 详细用户
河北 3 (01,a),(03,c),(05,e)
山东 2 (02,b),(06,f)
陕西 1 (04,d)最好只用一条SQL语句,对第三列的格式不做要求,类似表现就行。
第一次时间为:2453 第二次时间为:2781还是慢啊map15上的索引是什么? 建议做测试的时候使用同个表,否则很容易导致判断结果混乱。版主,救命啊http://bbs.csdn.net/topics/390520778