Un-indexing fields for performance?

[xochi]

As I develop a big solution, I often find myself doing searches in raw tables for various debugging purposes, which often triggers the auto-indexing for that field. Once the solution is tested & debugged, however, many of these field indexes are no longer needed and are probably causing performance drains in use. How do you deal with this situation? Manually turning off field indexes? Is there an easy way to turn off ALL field indexing, leaving the ones that need re-indexing set to auto-create so they will regenerate when needed?

[mr_vodka]

I usually just figure out which ones will be needed for either a minimal or full index, and then manually turn off all the other ones.

The fields that need to be indexed besides the obvious key fields are marked in my case with a suffix so that I can keep track of them.

There is no way that I know of that you can systematically turn off all indexing.

[Søren Dyhr]

Except by making all request entirely to a cartesian product field (calc)

But the question is if the CFs here really are necessary:

http://www.briandunning.com/cf/209

http://www.briandunning.com/cf/208

...or Daniele's suits the purpose better, if the number of fields are less than 5 would I say you just hardwire the calc.

http://www.briandunning.com/cf/277

This was a Devcon Chris Moyer topic in 2005 ... the issue should be that all the apparent redundant things from the field only occurs once as single words in the index.

Whether one long index really should flow faster over a networked solution should be a tad faster then transporting a series of smaller one, is a very good question ... which is up to Moyer to defend. But perhaps it makes sense???

--sd

Edited February 15, 2008 by Guest

[xochi]

Followup to an old discussion -- a related question. Let's say I go into my database design, and turn OFF indexing for a bunch of fields, but leave the "create as needed" feature turned ON.

Let's say I accidentally turn OFF an index from a field that was used in a relationship:

1. Will FM let me do this w/o warning me?

2. Will it eventually re-create the index for that field?

3. Will it re-create the index in time so that any auto-enter/calc fields don't get bogus values?

In other words, I'm wondering if I'm risking disaster if I just go turn off (most) of the indexes in the tables, but leave the "create as needed" setting ON?

[mr_vodka]

As long as your leave it on for automatically create as needed, it should reindex. I dont believe it should effect your auto-calc fields.

Just keep in mind that if you are doing backend searches in fields, it will create an index with this option on... Even if its fields that you do not regularly use for finds.

And to add to your orig post... I really do wish there was an option to reindex currently indexed fields as a part of regular file maintenance...

[The Shadow]

In other words, I'm wondering if I'm risking disaster if I just go turn off (most) of the indexes in the tables, but leave the "create as needed" setting ON?

There is no harm to this at all. Everything will happen "in time". However, if you do this, you might want to use the application a little bit as the user to use it will cause all needed fields to create indexes, which will make the solution less responsive.

[xochi]

I can confirm that what you said is correct -- I intentionally de-indexed most of the fields, leaving "create automatically" on, then did file maintenance, then exercised the solution and ran a bunch of tests, and it does indeed appear as if FM is smart enough to recreate indexes when needed "just in time".

Shrunk my database files by 30% and it seems to be a bit snappier now too. :)