Basic mySQL Indexing question

[Craig Stadler]

I have a fairly large database on my (2 million records)
WIN32 mySQL 4.0.20

The question is:

I am adding large amounts of data to it (300,000 to 500,000 at a time) with
the standard (INSERT INTO) command.

Question is, do I have to reindex, and when and how often?
Does mySQL automatically build indexes on the fly as I add data?

If I do have to reindex, is there a command that will rebuild all the
indexes for that table (I have 4) ???

Thanks for ANY clarification.
Craig
cs********@hotmail.com

[Bill Karwin]

Craig Stadler wrote:

Question is, do I have to reindex, and when and how often?
Does mySQL automatically build indexes on the fly as I add data?
It should merge the new state of the table into the index as you add
data, but this is not the speediest process, and it might not result in
a well-balanced index.
If I do have to reindex, is there a command that will rebuild all the
indexes for that table (I have 4) ???

Yes, myisamchk can be used to do this. I think this page will help you:
http://dev.mysql.com/doc/mysql/en/Insert_speed.html

Regards,
Bill K.

[steve]

"Craig Stadler" wrote:

I have a fairly large database on my (2 million records)
WIN32 mySQL 4.0.20

The question is:

I am adding large amounts of data to it (300,000 to 500,000 at a time) with
the standard (INSERT INTO) command.

Question is, do I have to reindex, and when and how often?
Does mySQL automatically build indexes on the fly as I add data?

If I do have to reindex, is there a command that will rebuild all the indexes for that table (I have 4) ???

Thanks for ANY clarification.
Craig
cs********@hotmail.com

From the functionality standpoint, everything will work, and you don’t
have to do anything special for the indexes to be populated.

From the performance standpoint, if it is an issue, use delay key
write to defer writing the indecis to disk (a huge perfor. drain).

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Basic-...ict140718.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=472904