y**b
发帖数: 88 | 1 Hi,
I am new to MySQL. I have a MyISAM table with more than 60m records. It took
forever to rebuild the index on it. Is there anyway to speed it up? Google
said large value of key_buffer_size, myisam_sort_buffer_size helps. Any best
practice to set the proper value? Thanks in advance. | j*****n
发帖数: 1781 | 2 extra memory and disk space should be also important key. | y**b
发帖数: 88 | 3 found one hack on someone's blog.
(1) create the table (tbl1) without index, then load the data
(2) create another empty table (tbl2) which has same structure
as tbl1, plus index
(3) stop mysql server
(4) copy tbl2.frm, tbl2.MYI to tbl1.frm, tbl1.MYI
(5) myisamchk -r -q tbl1.MYI
Since it needs to stop mysql start, it won't work for us.
took
Google
best
【在 y**b 的大作中提到】
: Hi,
: I am new to MySQL. I have a MyISAM table with more than 60m records. It took
: forever to rebuild the index on it. Is there anyway to speed it up? Google
: said large value of key_buffer_size, myisam_sort_buffer_size helps. Any best
: practice to set the proper value? Thanks in advance.
| j*****n
发帖数: 1781 | 4 online indexing always time consuming.
how about use the hack you found as nightly/off hour batch?
【在 y**b 的大作中提到】
: found one hack on someone's blog.
: (1) create the table (tbl1) without index, then load the data
: (2) create another empty table (tbl2) which has same structure
: as tbl1, plus index
: (3) stop mysql server
: (4) copy tbl2.frm, tbl2.MYI to tbl1.frm, tbl1.MYI
: (5) myisamchk -r -q tbl1.MYI
: Since it needs to stop mysql start, it won't work for us.
:
: took
| y**b
发帖数: 88 | 5 Shutdown mysql is not an option for us, so we will build the index over
weekend instead.
【在 j*****n 的大作中提到】
: online indexing always time consuming.
: how about use the hack you found as nightly/off hour batch?
| I******e
发帖数: 101 | 6 Any reason not using InnoDB tables? |
|
