473,549 Members | 2,698 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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********@hotm ail.com
Jul 20 '05 #1
2 2066
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********@hotm ail.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2374
by: Bacci | last post by:
I have two tables. The first is "Locations" which has 52,000 zip codes w/cooresponding latitudes and longitudes. The second "Suppliers" has 2,000 company names and addresses. The user enters a zip code and the cooresponding latitude and longitude is return. $sql = "SELECT l.latitude, l.longitude "; $sql .= "FROM Locations l"; $sql .=...
0
5757
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default setting whereby the minimum amount of characters is 4 for a search. Being that we're government and full of TLA (three-letter acronyms), that is not...
3
6229
by: stan k. | last post by:
First of all i'm on a win32 platform using java. I also have mysql installed. My question isabout benchmarks and multiple inserts & selects SQLITE: http://www.sqlite.org/ HSQL: http://hsqldb.sourceforge.net I current have a mysql database of approx. 80mb. Each day I Insert approx .5mb of new records into this table, and I might also run...
4
3528
by: Nino Skilj | last post by:
I'm a bit confused about indexing using MySQL. I have something like this: Table: PEOPLE Values: user_id first_name last_name
2
1598
by: Craig Stadler | last post by:
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?
3
1463
by: nick | last post by:
Hi, I'm pretty much a database beginner and have what I think is a basic question: If I have a table which has a 'status' column and I can have say three statuses: "active", "pending", "inactive". Is it better to break these out into a different "Statuses" table? On the one hand it seems to me it would be better to be in a different
4
8876
by: Vic Cekvenich | last post by:
What would be performance of pgSQL text search vs MySQL vs Lucene (flat file) for a 2 terabyte db? thanks for any comments. ..V -- Please post on Rich Internet Applications User Interface (RiA/SoA) <http://www.portalvu.com> ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet,...
1
4837
by: jrs_14618 | last post by:
Hello All, This post is essentially a reply a previous post/thread here on this mailing.database.myodbc group titled: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode I was wondering if anybody has experienced the same issues
4
2055
by: Amar | last post by:
Hi All, I need to select data from a database table containing huge amount of data. Now I am storing data using one primary key and I am just using simple select statement, and this process gives me the output but it is taking long to execute the query. As much I had heared I want to use some indexing or cluster indexing which might help me...
0
7521
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7959
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7473
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6044
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5369
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1944
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1061
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
764
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.