e*******e
发帖数: 1837 | 1 最近开始学MySQL,发现包含COUNT()的query非常的慢.
我的query(两个table和要用到的column都已经index过了):
SELECT COUNT(*) FROM table_name AS t
INNER JOIN samples AS s ON t.ID = s.ID
GROUP BY s.group
在一个25,000 row 的表里运行这个query居然要20秒.大虾给看看有没有办法加快点?
多谢! |
p*****n
发帖数: 43 | 2 本来以为自己还懂一点，看了你的问题才知道自己什么都不懂:P
【在 e*******e 的大作中提到】
: 最近开始学MySQL,发现包含COUNT()的query非常的慢.
: 我的query(两个table和要用到的column都已经index过了):
: SELECT COUNT(*) FROM table_name AS t
: INNER JOIN samples AS s ON t.ID = s.ID
: GROUP BY s.group
: 在一个25,000 row 的表里运行这个query居然要20秒.大虾给看看有没有办法加快点?
: 多谢!
|
s*****w
发帖数: 2065 | 3 me too.
but I guess the problem is not "count".
whenever you use "inner join", it will become very slow.
try to avoid that. |
e*******e
发帖数: 1837 | 4 Thanks! I just found that's the problem as well. it's about 200 times faster
if i don't have to join the two tables. However, it took me 5 mins to
update the table with the column I'm grouping with. :-(
My query is:
UPDATE table_name AS t, sample AS s
SET t.group = s.group
WHERE t.ID = s.ID
Is there anyway to speed this up?
【在 s*****w 的大作中提到】
: me too.
: but I guess the problem is not "count".
: whenever you use "inner join", it will become very slow.
: try to avoid that.
|
s*****w
发帖数: 2065 | 5 没太看明白你的程序
应该是有办法的，不过我实在是忘了，两年没碰了
你尝试一下更新表格，在里面加列，这样就不用inner join了
然后再执行查询
faster
【在 e*******e 的大作中提到】
: Thanks! I just found that's the problem as well. it's about 200 times faster
: if i don't have to join the two tables. However, it took me 5 mins to
: update the table with the column I'm grouping with. :-(
: My query is:
: UPDATE table_name AS t, sample AS s
: SET t.group = s.group
: WHERE t.ID = s.ID
: Is there anyway to speed this up?
|
