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 |
|
