How to load a huge data set into newly created table?

Question

I'm trying to FULLTEXT index into my table. That table content 3 million records.It was very difficult to insert that index using Alter table statement or Create index statement. Therefor easiest way to create new table and 1st add index and load the data. How can I load existing table data into newly created table? I'm using Xammp MySql database.

Answer 1

INSERT INTO newTable SELECT * FROM oldTable;

After your new table and index on it is created. This is given you want to copy all columns. You can select specific columns as well.

Answer 2

I don't know why creating a full text index on an existing table would be difficult. You just do:

create fulltext index idx_table_col on table(col)

Usually, it is faster to add indexes to already loaded tables than to load data into an empty table that has indexes pre-defined.

EDIT:

You can do the load by using insert. The following will insert the first 100,000 rows:

insert into newtable
    select *
    from oldtable
    order by id
    limit 0, 100000;

You can put this in a loop (via a stored procedure in MySQL or at the application level). Perhaps this will return faster. Each time you run it, you would change the offset value in limit.

I would expect that the overall time for creating an index would be less than using insert, but for your purposes, you might find this more convenient.