Persistent Index?

[JoSno]

Hi,

I have a pretty sizeable table, nothing massive but big enough to warrant an index being created on what would be the "foreign key".

The thing is that each day my program runs and it rebuilds the index and then drops it when it is completed, a process which takes about a minute, and I was wondering if this is being wasteful, and if in fact the index can just remain in place all the time? Will MySQL maintain the index as records are added/removed from the table? Or do I have to re-create the index before processing every day?

Thanks
Jo

 

[BNPMike]

If you don't drop it, MySQL won't.

 

[JoSno]

But then will it actually maintain the B-Tree or not?

 

[sleipnir214]

MySQL maintains all existent indeces as table content is modified.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BNPMike]

Both the data and the indexes are maintained on disc and written in real time. So both are always persistent give or take anything caught temporarily in caches and buffers. Generally it is best to maintain indexes. You can however drop them when you do bulk loads or reloads. It can be cheaper to build the index on the loaded data rather than as the data is loaded.