Scripting to remove indexes?

[Mikey79]

I'm working on a project here at work - Have a table with ~520 fields (not my work or design.) One problem is probably ~250 fields are indexed.

Is there an easy way or a way to make a script to unindex fields?

Using FM Pro 9 Advanced.

[IdealData]

I don't know of a scripted method, however consider why these fields are indexed.

A find request will index them. The target of a relationship must be indexed.

Are you short of disk space? AFAIK indexing is much preferred for improved system performance.

[Søren Dyhr]

The indexing gets on immediately when a search is done in the field, however is there in larger datamining solutions put in a cartesian product field where the searches then are made in instead.

To this can Daniele's CF be used that takes five values and permutate them into 120 lines... with the various combinations of field values. However is the provision of 500+ of fields, a genuine lack of relational structure - in the first place. So Daniele's function will even with tail recursion and a larger scope of fields never ever evaluate into something usefull, to prevent this substantial indexing requirement - as a note is:

69 ! = 1.71122452 × 10^98

The answer is unfortunate as it might sound, that the solution needs restructuring immidiately - if the indexing really is a problem. In a networked solution with WAN access ... is it indeed as far from a greased lightning as it can be!!

--sd

[Mikey79]

This is a "small" file from the "solution" that was made. It's 198megs. 2 other databases are 1gig each.

If each database has half it's fields indexed, the resulting smaller database will end up being faster.

You index to make the DB faster. If too much is indexed, you end up making the DB slower.

[Søren Dyhr]

Whopping small indeed, are you - beyond that the solution suffers from the spreadsheet syndrome, sure this is the right tool chosen at all. The sheer speed of MySQL or Valentina could to some extend compensate for the lack of a serious approach from the developers side.

--sd

[LaRetta]

Hi Mikey,

There is no way to automatically unindex. But you can control what is indexed and indeed I suggest you do. Here's a rough indea:

• If the field is searched a lot ... set it to index and leave 'auto index' on.
  
• If the field is rarely searched ... set it to idex NONE and leave 'auto index' off. You can still search on the field but it will be a bit slower, which is fine if you search on it only rarely (or only the Developer searches on it).
  
• If the field is a key in a relationship set the index to minimal and leave 'auto index' off.
  
• For fields which are indexed and don't need to be (you don't use them for searches or sorts very often or they aren't used as keys, or you have decided that they are used to search so rarely that you can afford to wait a few seconds longer) then by all means change them to Indexing None and uncheck 'automatic.'

Indexing, while decreasing time for searches etc, also increases the size of the database which can slow it down ... AND ... if the checkbox 'automatically create indexes' is ON then the index will continue to grow larger and larger, ie, it will never drop in size even if the index does - it maintains it's highest point forever until you repair it, as you are currently wanting to do. In my opinion, every time you create a field you should consider which storage option is most appropriate. It will make a big difference in speed and size of your solution. For more information, read FM Help under Defining field indexing options.

LaRetta :wink2:

[LaRetta]

AFAIK indexing is much preferred for improved system performance.

System performance is a relative term, Mark. As you know, just about everything is a weights and balances act. When a User waits because a field isn't indexed and they are searching, those few seconds take FOREVER. But at same time, if a User experiences slow system because it is sluggish and large due to unnecessary field indexing then that too slows a User down. Sometimes it is a 'pay me now or pay me later' and only consideration of the solution as a whole can best answer the question.

I agree with you. But I thought the wording could use clarification since performance is a two-edged sword we deal with every day.

LaRetta

[Mikey79]

Whopping small indeed, are you - beyond that the solution suffers from the spreadsheet syndrome, sure this is the right tool chosen at all. The sheer speed of MySQL or Valentina could to some extend compensate for the lack of a serious approach from the developers side.

--sd

I've been trying to migrate the data over to MySQL, but with so many fields it's very difficult. It's even hard to figure out what fields are necessary.

I hate coming into a project after someone else has solely built it over a long period of time.

Thank you LaRetta - I've got my work cut out for me.

[Søren Dyhr]

I hate coming into a project after someone else has solely built it over a long period of time.

Indeed it's going to be patch on patch, never the less is it sometimes the only chance you get to be in the sunshine, and have a regular income for some time. It usually requires substantial income from elsewhere to make conscientious objections - although it might be in place - with the daft way things are done here?

--sd