mos <[EMAIL PROTECTED]> wrote: At 06:23 PM 4/15/2007, you wrote: >Hi, > > I have a table with 2 million records but without an index or a primary > key. The column upon which I want to create an index is a varchar. Will > it give me any advantage when I have to do a select on the column? > > BTW, here is the scenario: > > create table `test` (`phonenumber` varchar(100)); > insert into test (phonenumber) values(`0001234567'); > and so on 2 million times
Indexes are the least of your worry. The code you've posted is extremely inefficient. The fastest way to load the data is using a "Load Data Infile" in which you could load the data in a few minutes. As it stands, it could take a few hours or even a couple of days. Every time the Insert statement is executed, it will lock the table, preventing others from reading from it. So the speed of accessing the table during the inserts is going to be very very slow. You'd be better off inserting the data into a temporary table and then if the data looks ok, insert it into the production table a few thousand rows at a time so it doesn't lock out people that are using Select statements. Use something like: insert into table1 select * from tmptable limit [offset,] 10000 and keep bumping up the offset each time its run. If no one else is using the table1, then just load all the data at once without the limit clause. > > alter table test add index (phonenumber) > > Also, I can't modify the code that calls the SQL. It was written in VB > and the developer has left the company with the source. It's a good thing that programmer left the company, because writing code like this would get him canned. If your company paid for the source, contact a lawyer and hunt him down for theft of company property. It shouldn't be too hard to figure out where he works. His behavior should also impress his new boss too. :) > > I would like to get your feedback before I actually do this because I > don't want to create a disruption when the DB is in production. > If you can't first load the data into a temporary table, then you'd better expect a lot of down time as it tries to load 2 million rows, 1 row at a time. If it were me, I'd rewrite the code from scratch. Make sure you have sufficient memory for key_buffer_size because building an index will take less time if it fits in memory. Good luck, and remember to make lots of coffee because it could take a while. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thank you Mike for your insightful comments. I've used the insert for illustration purposes. The actual production table gets inserted in batches. For instance, 1000 records on Monday, 1000 records on Saturday, etc. --------------------------------- Ahhh...imagining that irresistible "new car" smell? Check outnew cars at Yahoo! Autos.
