CODEWITHSUNDEEP
mysqlstored-procedurescursorbigdatadatabase
HichamEch
HichamEch

Reputation: 715

How to move data from table to an other in Mysql big table

i have a big table about 200,000,000 row MYISAM with a composed primary key but i have no index.

and i would like to create an index but the probleme is that the table server is broken when i execute the add index request : 

ALTER TABLE `db`.`table` 
ADD INDEX `index_0001` (`col1` ASC);

So i will create a new table and configure needed index and move data to the new table and rename it.

i tried this manip : 

INSERT INTO `db`.`table` (field1,field2,..) select field1,field2,....from eventdata limit 100000 offset 0 ;
INSERT INTO `db`.`table` (field1,field2,..) select field1,field2,....from eventdata limit 100000 offset 100000 ;
INSERT INTO `db`.`table` (field1,field2,..) select field1,field2,....from eventdata limit 100000 offset 200000 ;
.......... etc

But when the offset become more then 100,000,000 the query line response become more slow.

is there any other solution ?

tahnks

Upvotes: 0

Views: 61

Answers (1)

Wang Wen'an
Wang Wen'an

Reputation: 322

Use limit 100000 offset 100000 ; means MySQL searched the 100000 records and get the record from 100001;

Search the 100000 records is TABLE FULL SCAN, so it is very slow;

Use Primary key to search is better way, like ... where pk >=100000 and pk <= 199999

If Primary key can not divided into Number, use Join

select field1,field2... from tbname tb1,(select pk from tbname limit 100000 offset 100000)tb2 where tb1.pk = tb2.pk

Use Primary key to search the record is better than TABLE FULL SCAN;

Upvotes: 1

Related Questions