v****s
发帖数: 1112 | 1 目前的一个project需要在mysql里面查询 两个node之间的value,
table columns:
LOOKUPTABLE (INT id, VARCHAR node1, VARCHAR node2, INT value)
问题是这个table有 10 millions rows, 一次select query时间大概是0.8 sec：
select value from LOOKUPTABLE where node1 = 'a' and node2 = 'b';
尝试用index来优化query,但是不知道最优的index应该是哪种？谢谢！包子有赏！ | g*****g
发帖数: 34805 | 2 just index node1 and node2 columns.
【在 v****s 的大作中提到】
: 目前的一个project需要在mysql里面查询 两个node之间的value,
: table columns:
: LOOKUPTABLE (INT id, VARCHAR node1, VARCHAR node2, INT value)
: 问题是这个table有 10 millions rows, 一次select query时间大概是0.8 sec：
: select value from LOOKUPTABLE where node1 = 'a' and node2 = 'b';
: 尝试用index来优化query,但是不知道最优的index应该是哪种？谢谢！包子有赏！
| v****s
发帖数: 1112 | 3 i tried, but there is no significant improvement, still takes about 1 sec
per
query.
i used hashtable, maybe i should use btree?
【在 g*****g 的大作中提到】
: just index node1 and node2 columns.
| g*****g
发帖数: 34805 | 4 If you simply have too many records in a table, you may want to do
partitioning, it can be transparent to application. I am not a DB expert,
you can ask database board.
【在 v****s 的大作中提到】
: i tried, but there is no significant improvement, still takes about 1 sec
: per
: query.
: i used hashtable, maybe i should use btree?
| v****s
发帖数: 1112 | 5 thanks! dear bug bro!
【在 g*****g 的大作中提到】
: If you simply have too many records in a table, you may want to do
: partitioning, it can be transparent to application. I am not a DB expert,
: you can ask database board.
|
|
