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.<\/p>\n
So be sure you have enough free space for the complete DB ( MYI and MYD for MYISAM databases ) plus space for the new version.<\/p>\n
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 )<\/p>\n
Also increasing the Sort buffer sizes will greatly speed things up.<\/p>\n
Here is the SQL we used to reduce creation time from 4 days to 11 hours on a 400 Million record table (80Gig Size).<\/p>\n
set myisam_sort_buffer_size=2000000000;
\nset sort_buffer_size=2000000000;
\nalter table schema.tablename
\nadd index index1 using BTREE(field1,field2),
\nadd index index2 using BTREE(field3,field4),
\nadd index index3 using BTREE(field5);<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading