The most frequent way of adding an index to a table that does not yet
have one is to create an mfd_id
field and to then create an mfd_id_x
index on that field. The mfd_id
field is treated specially by Manifold to always contain unique values
in a table within a project. That process is described step
by step in the Adding
an Index to a Table topic. 
Tech tip: In most cases we will add an index by adding an mfd_id field. The process in this topic is not frequently done, but because it is useful sometimes and because it teaches more about the system it is worth studying.
However, if we want to copy records into different projects there is no guarantee there will not be duplicate values in the mfd_id fields for different records from different projects. The easiest way to deal with that is to create an index based on a field that is guaranteed to always have a unique value for each record no matter where in the universe it may go. To do that we can use a UUID field created as a computed field so that it is filled with guaranteed-unique values on creation.
We can use any field that contains non-NULL, unique values as the basis of a BTREE index. When we have a table that has no such fields in it we must add such a field to the table, both adding the field and also populating it with unique, non-NULL values at the same time.
We must create the field and also populate it with unique, non-NULL values at the same time because if we only created a new field using the Edit - Schema dialog without also populating it, the new field would be added to the table with NULL values for all records. Because of the NULL values it would not be usable as the basis of an index. Because of the continued lack of an index the new field would not editable either, so we could not replace the NULL values with unique, non-NULL values in each record.
The resulting dilemma is nothing new to DBMS people who routinely must deal with tables in almost all standard DBMS products that cannot be edited for lack of an index, but which do not have a suitable field in them to use for an index. The solution is to add a suitable field and to simultaneously fill it with unique, non-NULL values.
Manifold makes that easier than most systems because the Edit - Schema dialog allows us to create a new field and also at the same time to fill it with the value of a computed expression, that is, to add a computed field. In this case we want to use a computed expression that will fill the field with a guaranteed unique, non-NULL value for each record. See also the Example: Add a Computed Field to a Table topic.
One approach might be to use a computed expression that evaluates to a random number. However, even though random numbers might indeed be so pseudo-random they are not likely to repeat there is no guarantee that the output of a random number generator will not repeat. A better way is to use UUID (Universally Unique Identifier) values, which are guaranteed to be unique. Manifold makes that easy by providing the UuidMakeNew() function in SQL.
That is the technique we will use, as follows:
Consider the above table, which we have imported from a non-DBMS format that provides no indexes. As imported the table has no index so the light gray background indicates that the field values cannot be edited and records cannot be selected. To allow either selection or edits we must add a BTREE index.
If the table had a field with unique, non-NULL values already in it we could use that field to create an index. But this table contains only two fields and both of those fields have NULL values for some records, which means neither of the two fields can be used to create an index. We must add a new field that contains non-NULL values and only then can we use it to create an index.
We choose Edit - Schema to launch the Schema dialog. We will add a new field of type UUID to the table and simultaneously populate it using the the UuidMakeNew() function.
We click <new field> and in the Field box we give the new field the name U. We choose uuid in the Type box. We enter UuidMakeNew() into the Expression box. This tells Manifold to create the field as a computed field, automatically computing that expression to get the value of that field for each record in the table. We press Add to add the field to the table.
We now add a new index using the newly created field.
Note that the new field U appears in the schema listing with a small f as part of its icon to indicate it is computed with an expression. We click <new index> and in the Index box we give the new index the name U_x. We choose btree in the Type box. Click Add to add the index.
We will now instruct the new index to use the U field.
We click <new field> again and in the drop down box choose U as the Field to use.
We press Add to finish creating the index and then we press OK..
The result is that our table acquires a new computed field, called U, which in the act of creation was also populated with unique, non-NULL values for each record. The index we created on that field makes the table editable and selectable, as the white backgrounds of the DARMC and F3 columns indicate. Note that the U column retains a light gray background indicating it is not editable since the values in that column are automatically created by a computed expression using the UuidMakeNew() function.
That those values are long and meaningless UUID values does not matter since that field's sole purpose is to provide a convenient basis for an index so the table is editable and interactive selections can be made. For that purpose a BTREE index using the UUID computed field is fine. If we do not want to look at the U field in our table we can hide it.
Why special treatment of mfd_id? Manifold is often used with spatial data that originated in GIS or other systems which do not take as rigorous an approach to database matters as do enterprise class DBMS applications. It cannot be taken for granted that such data has indexes and much of it usually does not. To make it easier to apply more rigorous DBMS discipline within Manifold with such data the system makes use of a special case mfd_id field to provide a unique identifier for records and to always automatically build an index on mfd_id called mfd_id_x. This makes it convenient always to have an automatically-created unique identifier for records and an index on that identifier for data that is brought into Manifold from GIS formats and data sources that do not have indexes, but it does mean that to avoid collisions from attempting to create duplicate values of mfd_id we should use a different name in JOINs and similar. For example, use SELECT [mfd_id] AS mfd_id1... and not simply SELECT [mfd_id].
Why use an UUID field for an index instead of mfd_id? UUID values are guaranteed to be unique always so that records can be transferred between projects and tables even on different machines. A mfd_id value is unique per table, but not between different tables in the same project, and not between tables in different projects.
How to find the UuidMakeNew() function? If we do not remember the name of the function we can always look it up in the Query Builder tab of the Command Window.
Example: Add a Computed Field to a Table - In this example we add a field to a table. We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog. We then illustrate what happens when we fill a field statically with values using the Add Field option. Last, we show what the Add Component choice does in the New Field dialog.
Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.
Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.