Faster MYSQL Index creation

Creating indexes in mysql on large tables can take a long time. Because it makes a temporary copy of the entire DB file first then creates the indexes.

So be sure you have enough free space for the complete DB ( MYI and MYD for MYISAM databases ) plus space for the new version.

If you need to create multiple indexes doing them all in one command saves the time of creating that copy of the DB each time ( many hours in some cases )

Also increasing the Sort buffer sizes will greatly speed things up.

Here is the SQL we used to reduce creation time from 4 days to 11 hours on a 400 Million record table (80Gig Size).

set myisam_sort_buffer_size=2000000000;
set sort_buffer_size=2000000000;
alter table schema.tablename
add index index1 using BTREE(field1,field2),
add index index2 using BTREE(field3,field4),
add index index3 using BTREE(field5);

This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published.