How to remove and restore indexes when importing?

Stuart Wright

Well-known member
We're running a test import from AVForums into xenForo tomorrow (getting a dedicated server for the job and running on a copy of the database).
What is the process for removing the xenForo indexes prior to running the import and how do I then restore the indexes after the import is complete?
 
MySQL indexes?

You can run queries in this form:

ALTER TABLE table_name DISABLE KEYS;

and then to re-enable them:

ALTER TABLE table_name ENABLE KEYS;

Note that enabling them will take a while, as it actually has to build the index then.
 
Thanks Mike. I'll time it, obviously. Care to hazard a guess how long the import will take?

HP ProLiant DL360 G5
2 x Quad Core Processors 2.0Ghz
3GB Memory
2 x 146GB Drives (SAS) 10K Disks (RAID 1)

Threads: 1,257,393, Posts: 13,331,447, Members: 318,844 into a clean install of xF RC3.

The server will be private so no access from any users during the import process.
 
Well, disabling the keys may help speed it up a fair amount - I don't know of anyone else that's done that for the import (just the search index rebuild) - but I'm really not sure. It will be a number of hours, possibly around a day!
 
Only a day is not so bad. Not good to have the forums down for a day, but not that bad either.
You'll have considered this, but is it possible to do an import from a snapshot of the database and then do an secondary, incremental import from the live forum? That way the bulk of the data is imported in the first pass and the relatively small number of changes which happened over the following day in the second. Then the forums need not be down at all.
 
MySQL indexes?

You can run queries in this form:

ALTER TABLE table_name DISABLE KEYS;

and then to re-enable them:

ALTER TABLE table_name ENABLE KEYS;

Note that enabling them will take a while, as it actually has to build the index then.

I forgot to do this when I started my import - having paused the import and disabled keys it has almost doubled the speed of each import iteration ... (y)
 
Top Bottom