471,887 Members | 1,126 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,887 software developers and data experts.

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 1985
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
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 YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.