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]
