469,575 Members | 1,662 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

Basic mySQL Indexing question

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
Jul 20 '05 #1
2 1517
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.
Jul 20 '05 #2
"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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Bacci | last post: by
reply views Thread by Phil Powell | last post: by
3 posts views Thread by stan k. | last post: by
reply views Thread by James Walters | last post: by
4 posts views Thread by Nino Skilj | last post: by
2 posts views Thread by Craig Stadler | last post: by
3 posts views Thread by nick | last post: by
4 posts views Thread by Vic Cekvenich | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.