由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - 新学mysql,请教一个时间估计
相关主题
[Mysql] how to return NULL count in group by query (转载)mysql rebuild index very slow
问一个Oralce index的问题[合集] 问一个Oralce index的问题
help needed关于in的效率
help about SQL for ACCESSMYSQL 的LOG SIZE怎么在不停 变大
厚脸皮的来请教一道很初级的题..mysql 如何更新一个表的index啊?
跪求高人指点:一道SQL题3/5个包子可以么?先谢了!MySQL 5.0 cluster question
我也问一个sql querry的问题问个牛人才能解答的问题,关于mysql的内存
想练习SQL, 求建议请问这个query该怎么实现?
相关话题的讨论汇总
话题: qlocus话题: select话题: counts
进入Database版参与讨论
1 (共1页)
C***U
发帖数: 2406
1
select *, max(qEnd - qStart)
from
(select qFileID,qLocus,qTranscript,qLength,sFileId,sLocus,sTranscript,
sLength,qStart,qEnd,sStart,sEnd
from bj10dcmegablast
where (qLocus, qTranscript)
in
(select distinct qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1)) as middle2
group by qLocus,sLocus;
bj10dcmegablast有2千万条记录
我测试了
select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6
大概一分钟
测试了
select distinct qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1
大概2分钟
第一个那个复杂的query大概要多少时间啊?
谢谢指教了
a9
发帖数: 21638
2
请神仙出马

【在 C***U 的大作中提到】
: select *, max(qEnd - qStart)
: from
: (select qFileID,qLocus,qTranscript,qLength,sFileId,sLocus,sTranscript,
: sLength,qStart,qEnd,sStart,sEnd
: from bj10dcmegablast
: where (qLocus, qTranscript)
: in
: (select distinct qLocus, qTranscript
: from
: (select qLocus, qTranscript, count(distinct sFileID) as counts

C***U
发帖数: 2406
3
谁是神仙啊?
因为我这个是在个人电脑上跑的
所以想先估计一下时间多少
那我就可以放在那里 让他跑了
然后我可以去干点别的事情了

【在 a9 的大作中提到】
: 请神仙出马
C***U
发帖数: 2406
4
select qFileID,qLocus,qTranscript,qLength,sFileId,sLocus,sTranscript,
sLength,qStart,qEnd,sStart,sEnd
from bj10dcmegablast
where (qLocus, qTranscript)
in
(select distinct qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1)
这一层 已经跑了很久了。。。。
有谁能帮忙解答一下么?

【在 a9 的大作中提到】
: 请神仙出马
B*****g
发帖数: 34098
5
select "distinct" (这个有啥用?) qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1
C***U
发帖数: 2406
6
it will reduce the number of rows in result. Then speed up later search

【在 B*****g 的大作中提到】
: select "distinct" (这个有啥用?) qLocus, qTranscript
: from
: (select qLocus, qTranscript, count(distinct sFileID) as counts
: from bj10dcmegablast
: group by qLocus, qTranscript
: having counts > 6) as middle1

M*****r
发帖数: 1536
7
my (old) impression is that mysql doesn't have cost estimation in the
explain output
but you may still get some idea by looking at the estimated rows returned
it may not matter much how big the table is comparing to the execution plan/
indexes/stats

【在 C***U 的大作中提到】
: select *, max(qEnd - qStart)
: from
: (select qFileID,qLocus,qTranscript,qLength,sFileId,sLocus,sTranscript,
: sLength,qStart,qEnd,sStart,sEnd
: from bj10dcmegablast
: where (qLocus, qTranscript)
: in
: (select distinct qLocus, qTranscript
: from
: (select qLocus, qTranscript, count(distinct sFileID) as counts

C***U
发帖数: 2406
8
昨天晚上执行了一遍
4个小时执行完

plan/

【在 M*****r 的大作中提到】
: my (old) impression is that mysql doesn't have cost estimation in the
: explain output
: but you may still get some idea by looking at the estimated rows returned
: it may not matter much how big the table is comparing to the execution plan/
: indexes/stats

a*********8
发帖数: 9
9
看上去时间似乎太长了,exec plan里面有何问题没?

【在 C***U 的大作中提到】
: 昨天晚上执行了一遍
: 4个小时执行完
:
: plan/

B*****g
发帖数: 34098
10
已经group by qLocus, qTranscript了,还有dup的吗?

【在 C***U 的大作中提到】
: it will reduce the number of rows in result. Then speed up later search
C***U
发帖数: 2406
11
o
没有了。。。没仔细想找个地方
谢谢!!

【在 B*****g 的大作中提到】
: 已经group by qLocus, qTranscript了,还有dup的吗?
y*****g
发帖数: 677
12
如果太慢,可能要重写语句。
MYSQL 对 sub-query, 优化不好,
show indexes from yourtable;
show create table yourtable;
explain your query, will help
1 (共1页)
进入Database版参与讨论
相关主题
请问这个query该怎么实现?厚脸皮的来请教一道很初级的题..
CINAOUG/CINASSUG MySQL 2013讲座跪求高人指点:一道SQL题3/5个包子可以么?先谢了!
请问MySQL 可以快速处理table有1亿条数据么?我也问一个sql querry的问题
Help想练习SQL, 求建议
[Mysql] how to return NULL count in group by query (转载)mysql rebuild index very slow
问一个Oralce index的问题[合集] 问一个Oralce index的问题
help needed关于in的效率
help about SQL for ACCESSMYSQL 的LOG SIZE怎么在不停 变大
相关话题的讨论汇总
话题: qlocus话题: select话题: counts