# HUGE Tables and Indexes **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 17, 2009, 9:57am UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176 "2009-05-17T09:57:16Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![laurence](https://avatars.discourse-cdn.com/v4/letter/l/5fc32e/32.png) [@laurence](https://forums.percona.com/u/laurence)\ **Post date:** [May 17, 2009, 9:57am UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/1 "2009-05-17T09:57:16Z")
I have been given the task of loading 6.5billion records (yes, that is not a typo, six billion records) into mysql database running on Ubuntu 8.04. I have successfully done so using: load data local infile ‘FILENAME’ into table TABLENAME FIELDS TERMINATED BY ‘,’; This took around 10 hours. I used the following procedure to create the INDEXES: Create the data without indexes Move (rename) the table.MYD file to a backup Create an empy MYD file using TRUNCATE TABLE Add indexes Move the backed up MYD file back to table.MYD Run to fix: myisamchk -q -r -O key\_buffer=4096M -O sort\_buffer=2048M -O read\_buffer=640M -O write\_buffer=640M This procedure took around 15 HOURS Once complete file sizes: MYD → 410Gig MYI → 200Gig Server SPEC: DELL 2950 Dual Quad Core 2.5Mhz 32Gig RAM Data on 7 x 7200kRPM (STRIPE) iSCSI Connected Disks. At the end of the project I will have 10 times the amount of data to load. I would be interested to hear comments/ideas on ways to improve the load and index time. Or any other suggestions working with this quantity of data. Thanks Laurence ---
**Author:** ![arya](https://avatars.discourse-cdn.com/v4/letter/a/a4c791/32.png) [@arya](https://forums.percona.com/u/arya)\ **Post date:** [May 17, 2009, 12:50pm UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/2 "2009-05-17T12:50:30Z")
It may be possible, depending on the nature of the data, to shard it to multiple tables (and servers). Doing that should decrease indexing time, and maybe slightly insertion time. Is there a reason you’re using MyISAM? Or would you be able to look into other storage engines? ---
**Author:** ![laurence](https://avatars.discourse-cdn.com/v4/letter/l/5fc32e/32.png) [@laurence](https://forums.percona.com/u/laurence)\ **Post date:** [May 17, 2009, 2:55pm UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/3 "2009-05-17T14:55:22Z")
Yes, I could consider using another storage engine. What would you suggest? Can you elaborate a little on the sharding? ---
**Author:** ![januzi](https://avatars.discourse-cdn.com/v4/letter/j/82dd89/32.png) [@januzi](https://forums.percona.com/u/januzi)\ **Post date:** [May 17, 2009, 5:14pm UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/4 "2009-05-17T17:14:09Z")
So, You need to import data and indexes. You could create temporary table without any index and main table with indexes. After that, You import rows to the temporary table. This may take a while. Then You create script that fetches some rows from temporary table and puts them to the main table. You could run that script manually, but cron should do the trick. For example: - - - - - /path/to/the/php /path/to/the/script will run importing script every minute. This should be faster than playing with files and fixing indexes. ---
**Author:** ![laurence](https://avatars.discourse-cdn.com/v4/letter/l/5fc32e/32.png) [@laurence](https://forums.percona.com/u/laurence)\ **Post date:** [May 18, 2009, 12:56am UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/5 "2009-05-18T00:56:10Z")
Januzi, 1. Why would that be faster? 2. Can you explain the import script? 3. Why would I need to cron it, would this not be a one time action? ---
**Author:** ![tute666](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tute666/32/811_2.png) [@tute666](https://forums.percona.com/u/tute666)\ **Post date:** [June 9, 2009, 6:29pm UTC](https://forums.percona.com/t/huge-tables-and-indexes/1176/6 "2009-06-09T18:29:10Z")
will this data be modified? consider the Archive engine.