在我的应用当中,需要选出符合条件的记录
如SQL:
步骤1:
select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc
但是因为要用的分页,,所以说要先查出
步骤2:
select count(*) from(select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc) t所以我的步骤顺序为:
步骤2查询完成,接着步骤1:(步骤2>步骤1)
我用的mysql5,java语言做的开发
我目前的应用对效率要求很高..
请问一下能不能通过修改SQL语句或者是其他的办法来提高效率?
在线等!
如SQL:
步骤1:
select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc
但是因为要用的分页,,所以说要先查出
步骤2:
select count(*) from(select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc) t所以我的步骤顺序为:
步骤2查询完成,接着步骤1:(步骤2>步骤1)
我用的mysql5,java语言做的开发
我目前的应用对效率要求很高..
请问一下能不能通过修改SQL语句或者是其他的办法来提高效率?
在线等!
解决方案 »
- mysql调用存储过程返回值,求大神
- 求一个sum函数的sql语句
- 1个sql需要同时在 a b 机器上执行
- 关于Mysql的游标和临时表
- 如何根据SHOW VARIABLES/SHOW STATUS了解服务器运行状态,发现问题并作出相应调整?
- mysql分页存储过程
- 请教一个查询过慢的问题.
- MySql删除数据的问题
- ▄◣急!问高手"php+mysql程序从win2000移植到linux下后出现的两个简单小错误",最好请斑竹帮我解答一下!up也有分,分不够再加...
- 用 mysql events 每周日,晚上23点整。执行一个sql查询语句大致怎么写呢
- ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
- mysql 中存储过程动态生成sql语句执行的问题
select *,16 as tableNumber from merger_16 where gridID in
(55162406,55162407,55162408,55180406,55180407,
55180408,55198406,55198407,55198408)
order by
(
(lon-104.079048)*(lon-104.079048)
+(lat-30.658125)*(lat-30.658125)
) asc
步骤2代码:select count(*) from(
select *,16 as tableNumber from merger_16
where gridID in
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
) t
可以取得行数,不用SELECT COUNT(*)
+------+----------------------+
| id | c1 |
+------+----------------------+
| 1 | 2008-12-31按排期交货 |
| 2 | 2008-12-31????? |
| 3 | 2008-12-31????? |
| 4 | 2008-12-31????? |
| 11 | 111 |
| 9 | NULL |
| 20 | NULL |
+------+----------------------+
7 rows in set (0.05 sec)mysql> select FOUND_ROWS() ;
+--------------+
| FOUND_ROWS() |
+--------------+
| 7 |
+--------------+
1 row in set (0.08 sec)mysql>
不能通过select found_row()啊.
因为我要通过 求出的 count(*)
再来进行分页 limit begin,end
select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc;select FOUND_ROWS() :
select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc limit begin,end
select found_rows()into @ee;
select @ee
select found_rows()into @ee;
select @ee;
然后...
先 select count (*);然后我的程序根据count(*)出的结果来取得 limit 的begin 和end
如下:我还没得到count(*),不能进行分页啊!所以不能这么!
select found_rows()into @ee;
select @ee;
不太清楚你的实际需求了。
那为什么不直接先执行 select 得到结果集,然后直接用java mysql api中的函数得到这个记录总数?如果按照你的方法,MySQL本身不会,它是以SQL语句来缓存的。不过你可以把语句1的结果集存到一个临时表中,然后对这个临时表select count(*) ,但效率上好象不会有什么提高啊。
select *,16 as tableNumber from merger_16 where gridID in
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
select found_rows() into @ee;
UPDATE LSB SET BZ=@ee打开LSB的记录集,取BZ字段的值,假设LSB只有一个字段BZ
会不会用到缓存?
先查询select count(*) from( select *,16 as tableNumber from merger_16 where gridID in (55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408) order by ((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125)) asc ) t
再查询select *,16 as tableNumber from merger_16 where gridID in
(55162406,55162407,55162408,55180406,55180407,
55180408,55198406,55198407,55198408)
order by
(
(lon-104.079048)*(lon-104.079048)
+(lat-30.658125)*(lat-30.658125)
) asc limit 10,20
因为select count (*) from ( 语句2 ) t 和下面查询的是差不多的查询,只是加了 limit begin,end
这样会用到缓存不?
这2句
select count(*) from (
select *,16 as tableNumber
from merger_16 where gridID in
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
and kind like '0130%'
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
) t
select *,16 as tableNumber from merger_16
where gridID in
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
and kind like '0130%'
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
limit 0,10我在程序中打印出来2句都用了差不多的时间 31ms(我的CPU 是 P7350,2G内存)
select *,16 as tableNumber from merger_16 where gridID in
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
select found_rows() into @ee;
UPDATE LSB SET BZ=@ee这种方式嘛。
和
select * from table a where id>20
是两句不同的SQL语句,不会利用缓存。
感觉上,你还是用临时表解决比较方便。
比如说我将数据select出来后insert into 一个临时表?
那我还不是要
1.select * from 临时表
2.select count(*) from 临时表
这里不是更复杂吗?
但这样,从时间上来说,第二步的时候,就不需要再执行第一步的这个语句了。而从一个表中取得select count(*)的速度是很快的。
(55162406,55162407,55162408,55180406,55180407,55180408,55198406,55198407,55198408)
order by
((lon-104.079048)*(lon-104.079048)+(lat-30.658125)*(lat-30.658125))
asc
select found_rows() into @ee;
UPDATE LSB SET BZ=@ee
取LSB表中BZ字段值
如果是这样。 在你的SELECT语句中加上修饰SQL_CALC_FOUND_ROWS 试一下。加上SQL_CALC_FOUND_ROWS之后,即使你用了limit n,m ,SELECT FOUND_ROWS() 仍会返回满足条件记录的总数。这样,你执行完 select SQL_CALC_FOUND_ROWS 之后,再取一下记录总数就行了。
mysql> select * from calendar;
+------------+
| d |
+------------+
| 2006-11-01 |
| 2006-11-02 |
| 2006-11-03 |
| 2006-11-04 |
| 2006-11-05 |
| 2006-11-06 |
| 2006-11-07 |
| 2006-11-08 |
| 2006-11-09 |
| 2006-11-10 |
| 2006-11-11 |
| 2006-11-12 |
| 2006-11-13 |
| 2006-11-14 |
| 2006-11-15 |
| 2006-11-16 |
| 2006-11-17 |
| 2006-11-18 |
| 2006-11-19 |
| 2006-11-20 |
+------------+
20 rows in set (0.00 sec)mysql> select * from calendar order by d limit 10,5;
+------------+
| d |
+------------+
| 2006-11-11 |
| 2006-11-12 |
| 2006-11-13 |
| 2006-11-14 |
| 2006-11-15 |
+------------+
5 rows in set (0.00 sec)mysql> select FOUND_ROWS() ;
+--------------+
| FOUND_ROWS() |
+--------------+
| 15 |
+--------------+
1 row in set (0.03 sec)mysql>
mysql> select SQL_CALC_FOUND_ROWS * from calendar order by d limit 10,5;
+------------+
| d |
+------------+
| 2006-11-11 |
| 2006-11-12 |
| 2006-11-13 |
| 2006-11-14 |
| 2006-11-15 |
+------------+
5 rows in set (0.00 sec)mysql> select FOUND_ROWS() ;
+--------------+
| FOUND_ROWS() |
+--------------+
| 20 |
+--------------+
1 row in set (0.00 sec)mysql>