Adding an Index to a Table

To be editable, tables in Manifold must have at least one btree index on a key field. Tables must also have an index to enable interactive selection (as opposed to using SELECT in SQL, which does not require an index). That is usually done automatically for us, but sometimes we manually must add an index to a table. This can be done in one click using the Add Identity command in the Schema dialog.

In most cases when we bring a table into Manifold from some outside source it will either have a key field and index in the table or Manifold will automatically create those for us. Tables without a key field and an index are inconvenient: using them is slower, we cannot edit them and we cannot make selections in them. That is true of most database systems, including Manifold. To enable editing or selection in tables, we must add a key field (called an identity field) and a btree index to the table.

A table, or a drawing's table, must have a key field with a btree index (and not a btreedup or some other form of index) in it for selection to work. If the table does not have a btree index, selection will not be available.

Adding an identity field and a btree index to a table is such a common task that the Schema dialog provides an Add Identity command to do it in one click.

Adding an identity field and index using Add Identity:

Double-click on the table to open it.
Choose Edit - Schema.
In the Schema dialog toolbar, click the Add Identity button.
Press the Save Changes button.

By default, the Add Identity command adds a Manifold mfd_id identity field and a mfd_id_x btree index on that field.

Example

The field name mfd_id is a special name that is supported by special Manifold infrastructure. A field called mfd_id will be automatically populated by Manifold with unique values, as needed, to guarantee a unique, non-NULL value in that field for each record so it may be used as the basis of an index. This capability is specific to Manifold but it is very convenient when we work with tables within Manifold.

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 duplicates for some records, which means neither of the two fields can be used to create an index. In any event, it is quicker to simply use Add Identity.

We choose Edit - Schema to launch the Schema dialog.

We click the Add Identity button in the toolbar.

A new mfd_id field of type int64 and a new mfd_id_x index that is a btree index on the new mfd_id field are provisionally added to the schema. They are shown with bluish background color to indicate the addition of the field and index have not yet been committed to the schema. If we like what we have done, we press Save Changes to update the schema, to add the new field and index to the table.

The table immediately updates to show the new mfd_id field, which is autopopulated with unique numbers. The table now uses white background color since it is fully editable and selectable. Done!

Tech Tip: When the Add Identity command uses int64 as the type for a new mfd_id field that ensures we will never run out of unique values for that field: 64-bit integers allow for over a quintillion records with a unique id for each. Given that 32-bit integers allow just over two billion unique values (and records) using int32 as the Type for the new mfd_id field would not allow enough records for larger data. Manifold users work with tables that have more than two billion records. Manifold always creates mfd_id fields as int64 when they are automatically created. Most Manifold users will also "future proof" their indices when manually creating mfd_id fields by also creating them as int64 fields.

There is no performance downside to adding an mfd_id field to a table for use as an index: it is a system field that takes no additional space and does not reduce performance. For that reason, many people will use the Add Identity command to instantly add an mfd_id field and mfd_id_x even if there already is a field in the table that could be used as an identity field for a btree index.

Add an Index Using an Existing Field

The recommended way to add an index to a table is to use the Add Identity command as described above. That takes only one click and gets the job done with little or no learning required. An alternative approach is to use an existing field in the table and add an index to that.

If a table already has a field, or a combination of fields, suitable for an identity field, providing a unique value for every record, we can use the Schema dialog to add an index for that field or combination of fields. This takes a few more clicks, and it requires a bit more thought, so many people prefer to always use the one-click wonderfulness of Add Identity.

Sometimes we import data from a format, such as CSV, which is used for interchange of database data but which lacks the ability to convey indexes. In such cases, there might be a field in the table that was an identity field and used for an index in the originating database. If we like, we can add an index using such an existing field.

We add a new index by pressing the Add button in the Schema dialog and choosing Index in the drop down menu.

Adding an index using an existing field:

Double-click on the table to open it.
Choose Edit - Schema.
In the Schema dialog toolbar, click the Add button and choose Index.
In the Index dialog provide a Name for the new index.
Choose regular index (btree) as the Type.
In the first Field menu choose the existing field to be used.
Press OK.
In the Schema dialog, press Save Changes.

By default, the Add Identity command adds a Manifold mfd_id identity field and a mfd_id_x btree index on that field.

Consider the above table, which we have imported from a CVS 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.

The table provides sample points of interest data from the UK Ordnance Survey, in this case, showing the locations of map objects such as points where public telephones are located. It contains a UNIQUE_REFERENCE_NUMBER field, which we guess in the original database contained a unique number for each record, and which we suppose was used as an identity field for an index on the table. We will use that field for an index.

We choose Edit - Schema to launch the Schema dialog.

We click the Add command.

In the drop down menu we choose Index.

In the Index dialog we provide a Name for the new index. We can use whatever legal name we like, but in the Manifold community a convention has arisen to name indexes using the name of the field or fields they use, plus an _x. We therefore name the new index unique_ref_number_x. When we look at the schema again in a few months or years the name will help us remember what we did, or at least much better than using a name like Ringo or George.

We choose regular index (btree) as the Type. In the drop down menu for Field we choose the UNIQUE_REFERENCE_NUMBER field. Press OK.

The new index appears in the schema as a provisional addition, shown in bluish background color. To commit the change to the table, we press Save Changes.

Manifold quickly builds the index (less quickly, but still impressively fast in the case of tables with billions of records...) and redisplays the table. If our guess that the UNIQUE_REFERENCE_NUMBER field was wrong, and it is unsuited as an identity field because it contains duplicates, Manifold will raise an error message Cannot add index. and would make no changes to the table.

In this case, we guessed correctly, and the table's claimed unique reference number field is, indeed, unique for each record. The table immediately appears. Because the table now has an index the background of the table has changed to white, indicating that it may be edited and that interactive selection is enabled.

We can reorder, show/hide and resize the columns in the table by using the Layers pane.

Notes

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. Much data used in the GIS world does not. To make it easier to apply sensible DBMS capabilities within Manifold, the system makes use of a special mfd_id field to provide a unique identifier for records. Manifold also uses 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.

As with any identity field that must be unique, we should keep in mind the need to avoid collisions from duplicate values when using SQL features like JOIN. For example, use SELECT [mfd_id] AS mfd_id1... and not simply SELECT [mfd_id] in JOINs. A mfd_id value is unique per table, but not between different tables in the same project, and not between tables in different projects.

Alternatives to mfd_id - Manifold's special treatment of an mfd_id field is very useful, but it is an example of applied magic unique to Manifold. Sometimes we might want to add a key field to a table to be used for an index which does not in any way rely on special features built into Manifold. For a new field hosted in an external database that supports autogenerated fields, we could create a new field as an autogenerated field (the Schema dialog can do that).

A more complex but more general way to create an index for a table is to create a new, computed field that is populated upon creation with UUID values. That allows using tables with such indexes in other projects on other machines because the field used for the table's index is guaranteed to always have unique values. That procedure is described step by step in the Example: Add a UUID-based Index to a Table topic.

Automatic Creation of Indexes on Import - Manifold automatically creates an index as necessary when importing tables into a project from many spatial data sources used in GIS work, for example, when importing a drawing. Most Manifold dataports that import data will automatically add an mfd_id key field and an mfd_id_x index to tables. When we create a new, blank table in a Manifold project, Manifold will by default create that table with an mfd_id key field and an mfd_id_x index.

When we import data from sophisticated data sources, such as a DBMS, we usually get a table with an index as well, since DBMS people routinely will include indices for performance in their native database systems. Manifold of course will import and will utilize those indices along with the rest of the schema and data.

But if we import a table from a simple data format like CSV that does not allow indexes, then Manifold will not automatically add an index, in respect of keeping the data exactly as it was in the original form. In that case when we pop open the table we will see it with gray background color that indicates it cannot be edited.